CASE statement

Lets you conditionally return specific values from multiple comparisons.

There are two types of case statements:

A simple case statement that compares an expression to a set of simple expressions to return specific values.
A searched case statement that evaluates a set of Boolean expressions to return specific values.

Syntax

Simple CASE statement:

CASE [input_expression]  
WHEN when_expression_1 THEN when_true_result_expression_1  
WHEN when_expression_2 THEN when_true_result_expression_2 
[...n]  
ELSE else_result_expression --this statement is optional
END

Search CASE statement:

CASE   
WHEN Boolean_expression_1 THEN when_true_result_expression_1  
WHEN Boolean_expression_2 THEN when_true_result_expression_2 
[...n]  
ELSE else_result_expression --this statement is optional
END

Arguments

If there is no ELSE clause, and all WHEN clauses are evaluated as false, the result is an empty cell.

input_expression	A Multidimensional Expressions (MDX) expression that resolves to a scalar value.
when_expression	A specified scalar value against which the input_expression is evaluated, which, when evaluated to true, returns the scalar value of the else_result_expression.
when_true_result_expression	The scalar value is returned when the WHEN clause evaluates to true.
else_result_expression	The scalar value returned when none of the WHEN clauses evaluate to true. This argument is optional.
Boolean_expression	An MDX expression that evaluates to a scalar value.

Examples

Example of a simple case statement

If Sprint is closed, the boolean value will be 1, and the calculation will return "Yes", otherwise "No" will be returned:

--example of "Simple" case stetement
CASE
  [Sprint].CurrentMember.GetBoolean('Closed')
WHEN CBool(1) THEN 'Yes'
WHEN CBool(0) THEN 'No'
END

Example of a search case statement

Calculates the percentage of resolved issues from issues created. In the following example, a logical expression in the case statement is used for the measure "Issues created" to avoid dividing by zero, which returns infinity.

--example of "Search" case stetement
CASE WHEN
  [Measures].[Issues created] <> 0
THEN
  CoalesceEmpty([Measures].[Issues resolved], 0)
  /
  [Measures].[Issues created]
END

Example of a case statement with ELSE branch

If the Due date is empty, then 60 days are added to the Issue created date; otherwise, the due date is returned. Both result_expressions should be in the same format, or the format must be manually defined:

--example of "Search" case stetement
CASE WHEN
  IsEmpty([Measures].[Issue due date])
THEN
  DateAddWorkdays(
    [Measures].[Issue created date],
    60
  )
ELSE
  DateParse([Measures].[Issue due date])
END

Read more details about DateAddWorkdays() and DateParse() functions.