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