Syntax

IIF ( search_condition, true_part, false_part )

Parameters

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 1

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:

Example 2

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: