Filtering a Measure (or Removing Outliers)
Say I have a measure, foo, in a cube, and I have a reporting requirement that users want to see the following measures in a report:
total foo
total foo excluding instances where foo > 10
total foo excluding instances where foo > 30
What is the best way to handle this?
In the past, I have added Named Calculations which return NULL if foo > 10 or just foo otherwise.
I feel like there has to be a way to accomplish this in MDX (something like Filter([Measures].[foo], [Measures].[foo] > 10)), but I can't for the life of me figure anything out.
Any ideas?
Answer
The trick is that you need to apply the filter on your set, not on your measure.
For example, using the usual Microsoft 'warehouse and sales' demo cube, the following MDX will display the sales for all the stores where sales were greater than $2000.
SELECT Filter([Store].[Stores].[Store].members, [Unit Sales] > 2000) ON COLUMNS, [Unit Sales] ON ROWS FROM [Warehouse and Sales]