The following examples show an axis filter that uses just measure items and an axis filter that uses a measure item and an attribute relationship. A table containing more example expressions follows these two examples.
This axis filter on columns returns columns where Avg Selling Price is greater than or equal to $75.00 and Profit is greater than $50,000.
[Measures].[Data6 (Avg Selling Price)]>=75 AND [Measures].[Data8 (Profit)]>50000
Here is the Expression window for the filter.
This axis filter on rows returns rows where the Product sales for the current period of the current year are greater than $150,000 and the Prod ABC Classification for the Product is A. The first part of the expression contains the sales measure item criteria, and the second part contains the attribute relationship criteria.
The IIF statement in the second part checks that the level for the attribute relationship referenced in the expression is visible in rows. In this case, the level is visible so Stratum.Viewer considers that filter condition when executing the filter. If the level had not been visible, the condition would have been ignored while executing the filter.
[Measures].[Data2 (Current Period This Year)]>150000 and IIF([Product].[Product].CurrentMember.Level.Name="Product",[Product].[Product].Properties("Prod ABC Classification")="A",1)
Note: The portion of the above MDX that checks for the level visibility references the name of the dimension and hierarchy for the level -- in this case [Product].[Product]. The hierarchy name is needed in cases where there are multiple hierarchies within the same dimension.
This is the Expression window for the filter.
Here's what the view would look like if you were to drill up to RepBroker. Only the [Measures].[Data2 (Current Period This Year)]>150000 part of the axis filter expression is executed since Product is no longer visible.
|
Desired Results... |
Example Axis Filter |
|
For all levels, Average Selling Price is >= 75 and Profit > 50,000. |
[Measures].[Data6 (Avg Selling Price)]>=75 And [Measures].[Data8 (Profit)]>50000
|
|
When at Product level, will return rows where Prod ABC Classification = “A” AND Actual Sales Sales Units Jan 2014 to Sep 2014 is > 150,000. When at any other level - Actual Sales Sales Units Jan 2014 to Sep 2014 is > 150,000. |
[Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)]>150000 and IIF([Product].[Product].CurrentMember.Level.Name="Product",[Product].[Product].Properties("Prod ABC Classification")="A",1) |
|
Only for the Product level, return rows where Prod ABC Classification = “A” AND Actual Sales Sales Units Jan 2014 to Sep 2014 is > 150,000. When at any other level – No filter applied. |
IIF([Product].[Product].CurrentMember.Level.Name="Product" , IIF ([Product].[Product].Properties("Prod ABC Classification")="A" AND [Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)]>150000, 1,0) , 1) |
|
When at Product level, will return rows where Actual Sales Sales Units Jan 2014 to Sep 2014 is > 150,000. When at any other level - Actual Sales Sales Units Jan 2014 to Sep 2014 is > 10,000,000. |
IIF([Product].[Product].CurrentMember.Level.Name="Product" , IIF ([Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)]>150000, 1,0) , [Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)] > 10000000) |
|
When at Product level, will return rows where Prod ABC Classification = “A” AND Actual Sales Sales Units Jan 2014 to Sep 2014 is > 150,000. When at any other level - Actual Sales Sales Units Jan 2014 to Sep 2014 is > 10,000,000. |
IIF([Product].[Product].CurrentMember.Level.Name="Product" , IIF ([Product].[Product].Properties("Prod ABC Classification")="A" AND [Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)]>150000, 1,0) , [Measures].[Data1 (Actual Sales Sales Units Jan 2014 to Sep 2014)] > 10000000) |