Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false.
Syntax
IIF ( search_condition, true_part, false_part )
Parameter | Description |
---|---|
search_condition | An expression to evaluate as true or false (see MDX Grammar Rules). |
true_part | A value_expression or a set. IIF returns this expression if the search condition evaluates to TRUE (something other than zero). The value_expression can be a numeric value expression or a string value expression. |
false_part | A value_expression or a set. IIF returns this expression if the search condition evaluates to FALSE (zero). The value_expression can be a numeric value expression or a string value expression. |
Example
The company plans an expensive promotion of its caffeinated drinks. For the Caffeinated products only, the following query calculates a Revised Budget that is 110% of the regular budget.
WITH MEMBER [Scenario].[Revised Budget] AS 'IIF ( [Product].CurrentMember.Caffeinated, Budget * 1.1, Budget )' SELECT {[Scenario].[Budget], [Scenario].[Revised Budget]} ON COLUMNS, [Product].Levels(0).Members ON ROWS FROM Sample.Basic WHERE ([Measures].[Sales], [Year].[Qtr3])
This query returns the grid:
(axis) | Budget | Revised Budget |
---|---|---|
100-10 | 18650 | 20515 |
100-20 | 8910 | 9801 |
100-30 | 3370 | 3370 |
200-10 | 11060 | 12166 |
200-20 | 9680 | 10648 |
200-30 | 3880 | 3880 |
200-40 | 2660 | 2660 |
300-10 | 10600 | 11660 |
300-20 | 3760 | 4136 |
300-30 | 8280 | 9108 |
400-10 | 7750 | 7750 |
400-20 | 6800 | 6800 |
400-30 | 3290 | 3290 |
100-20 | 8910 | 8910 |
200-20 | 9680 | 9680 |
300-30 | 8280 | 8280 |
The following query calculates a Revised Budget equaling Budget for caffeinated products, and Actual for non-caffeinated products.
WITH MEMBER [Scenario].[Revised Budget] AS 'StrToMbr(IIF ( [Product].CurrentMember.Caffeinated, "Budget" , "Actual" ))' SELECT {[Scenario].[Budget], [Scenario].[Revised Budget]} ON COLUMNS, Children([100]) ON ROWS FROM Sample.Basic WHERE ([Measures].[Sales], [Year].[Qtr3])
This query returns the grid:
(axis) | Budget | Revised Budget |
---|---|---|
Cola | 18650 | 18650 |
Diet Cola | 8910 | 8910 |
Caffeine Free Cola | 3370 | 3189 |