#
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

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

Calculates the percentage of resolved issues from issues created:

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

See more about CoalesceEmpty() here

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.

## See also

- Description of conditions in calculated measures
- Condition statements with function Iif()