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)