The IIf function
evaluates a logical expression and returns one of two specified values
based on the result.
Returns: A value.
Parameters
- LogicalExpression
- Specifies the logical expression to evaluate.
- Expression1
- Specifies the expression to evaluate, and whose value to return, if the logical expression is true (nonzero).
- Expression2
- Specifies the expression to evaluate, and whose value to return, if the logical expression is false (zero).
Description
You can specify MDX expressions that evaluate to any type of value for the Expression1 and Expression2 parameters. However, if either expression evaluates to null, and its value is to be returned, the IIf function will return the numeric value zero.
Example
In this example you want to use the Iif function to determine the profit amount and profitability of your stores. The following query returns a profitability indicator as "High" if the profit amount is greater than 80000 and "Low" if it is less than 80000.
Query
WITH
MEMBER [Price Analysis].[Measures].[Profitability]
AS IIf([Price Analysis].[Measures].[Profit Amount] > 80000, "High", "Low")
SELECT {[Price Analysis].[Measures].[Profit Amount],
[Price Analysis].[Measures].[Profitability]}
ON AXIS(0)
, {Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES)} ON AXIS(1)
FROM [Price Analysis]Result
| Store | Profit amount | Profitability |
|---|---|---|
| ValueTrend Store 554 | 56360.49 | Low |
| ValueTrend Store 782 | 69077.9 | Low |
| ValueTrend Store 1414 | 80710.51 | High |
| ValueTrend Store 835 | 58189.07 | Low |
| ValueTrend Store 1095 | 81673.1 | High |
| ValueTrend Store 1199 | 84813.24 | High |
| ValueTrend Store 116 | 81950.42 | High |
| ValueTrend Store 278 | 75186.35 | Low |
| ValueTrend Store 375 | 77676.97 | Low |
| ValueTrend Store 681 | 79151.31 | Low |
| ValueTrend Store 875 | 77360.12 | Low |