IIf Function
The IIf function in Multidimensional Expressions (MDX) can be used to perform simple, yes-or-no decisions. For example, consider the following MDX query example.
WITH MEMBER [Measures].[BigSeller] AS
'IIf(Measures.[Store Sales] > 20000, "Yes", "No")'
SELECT
{[Store].[Store Name].Members} ON COLUMNS,
{[Measures].[Store Sales], [Measures].[BigSeller]} ON ROWS
FROM Sales
The MDX query example returns two rows for each store in the Sales cube. One row, the [Measures].[Store Sales] member, supplies the total store sales for each store. The second row is a calculated member that, based on the store sales for each store, determines if the store is a "big seller". That is, the IIf function is used to check a simple yes-or-no condition. In this case, the condition is whether or not the store sales figure for each store is greater than $20,000.00. If it is, the value of the member for that store is Yes. If the store sales figure is equal to or less than $20,000.00, it returns the value No.
This is a simple but graphic example of the use of the IIf function to return different values based upon a single Boolean condition; other MDX functions and operators can be used to supply the returned values in the IIf function.
For more information about the syntax of the IIf function, see IIf.