#
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 evaluate to 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 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 boolan value will be 1 and calculation will return "yes", otherwise "No"

--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 Due date is empty then 60 days are added to Issue created date, otherwise the due date is returned. Both result_expressions should be in the same format or 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

See more about DateAddWorkdays() and DateParse() here

## See also

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