Skip to main content
Version: I2023.1.0

CASE

The CASE function evaluates each row in the dataset according to one or more input conditions, and outputs the specified result when the input conditions are met.

Syntax

CASE input_attribute WHEN condition THEN output_expression [...] [ELSE other_output_expression] END

OR

CASE WHEN input_expression THEN output_expression [...] [ELSE other_output_expression] END

Return Value

Returns one value per row of the same type as the output expression. In calculated measure expressions, all output expressions must be a measure.

Input Parameters

input_attribute
The measure attribute whose values you want to evaluate.

WHEN input_condition or input_expression
Required. The WHEN keyword is used to specify one or more Boolean expressions. If an input value meets the condition, then the output expression is applied. The input can be a dimension or a measure.

THEN output_expression
Required. The THEN keyword is used to specify an output expression when the specified conditions are met. The output expression must be a measure.

ELSE other_output_expression
Optional. The ELSE keyword can be used to specify an alternate output expression to use when the specified conditions are not met. The output expression must be a measure. If an ELSE expression is not supplied, ELSE NULL is the default.

END
Required. Denotes the end of CASE function processing.

Examples

Return a different shipping rate measure based on the value in the region dimension:

CASE WHEN [Region].CurrentMember.Name="West" THEN [Measures].[ShipRate1] WHEN [Region].CurrentMember.Name="East" THEN [Measures].[ShipRate2] ELSE [Measures].[ShipRate3] END