IIF

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

Syntax

IIF(condition_expression,then_expression,else_expression)

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

condition_expression
A Boolean expression that evaluates to true or false.

then_expression
The expression to evaluate if the condition is true.

else_expression
The expression to evaluate if the condition is false.

Examples

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.

IIF([Measures].[order_quantity]=0,NULL,[Measures].[total_sales]/[Measures].[order_quantity])

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)