IIf function often used in MultiDimensionalExpression (MDX), It’s quiet simple to implement single block of IIf block. This an example of Multiple IIf logic block. Example1:
|
1 2 3 4 |
IIf(Measures.[Store Sales] > 50000, "Good", IIf(Measures.[Store Sales] > 30000, "Average", IIf(Measures.[Store Sales] > 10000, "Poor", "No"))) |
A logical script in MDX to perform an “If..Then.. Else..” block.
|
1 |
IIf(«Logical Expression», «Numeric/String Expression1», «Numeric/String Expression2») |
Example1:The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), 0, 1) |
Example2:The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No") |
Example3:
|
1 2 3 4 5 |
IIf( [Scenario].CurrentMember IS [Scenario].[Budget], [Measures].[Budget Unit Sold], [Measures].[ActualUnit Sold] ) |
Example4:Average Sales (Measure) By Time Day (Dimension)
|
1 2 3 4 5 6 7 |
IIf ( ([Time].currentmember.level.name = "(All)" OR [Time].currentmember.level.name = "Year" OR [Time].currentmember.level.name = "Quarter" OR [Time].currentmember.level.name = "Month" OR [Time].currentmember.level.name = "Day" ),AVG(Descendants(Time.CurrentMember,[Time].[Day]),Measures.[Sales]),0) |
Source:… Continue reading