The IIF function evaluates one of two different expressions, depending on whether a Boolean condition is true or false.



Return Value

Returns the results of the first expression if the condition is true, otherwise returns the results of the second expression if the condition is false.

Input Parameters

A Boolean expression that evaluates to true or false.
The expression to evaluate if the condition is true.
The expression to evaluate if the condition is false.


In MDX expressions, you cannot divide a number by zero - it returns an error. A very common use of IIF is to handle 'division by zero' errors within calculated measures. This expression returns NULL if order quantity is zero, otherwise calculates the average item price per order.


This expression returns a score of 1 when the measure Internet Sales Amount is greater than 10000, otherwise returns a score of 0:

IIF([Measures].[Internet Sales Amount]>10000, "1", "0")

This expression normalizes the order quantity for items sold prior to 2005 (in 2005 and earlier, items were sold in boxes of 5, so one unit was really 5 items):

IIF([Order YearMonth].CurrentMember.Name < 2005, Measures.orderquantity1 * 5,           Measures.orderquantity1)