Answered by:
[SSAS  MDX] Evaluate a measure over a unique combination of different dimension members
Question

I wish to evaluate a measure over a unique combination of different dimension members.
For example, I wish to look at the measure 'Costs' over three dimensions: 'Product', 'Category', 'Region'.
I need to evaluate if the costs for each unique combination of product, category and region (based on a dimension attribute) is larger than a specific value (e.g., 1000).
If it is (e.g., 1500), I want to keep the difference (500) in a calculated measure.
Is this possible with MDX? Or should I look to prepare this kind of calculations beforehand (in ETL).
Thanks,
Answers

It is possible, but you have to test the performance on you cube. Perharps Aggregations can help but relational it would be the fastest way.
Try this:
with member [Measures].[Cost2] as [Measures].[Cost]1000; create member currentcube.[Measures].[Diff over 1000] as sum( filter( existing ([Dim Product].[Product].[Product]* [Dim Category].[Category].[Category]* [Dim Region].[Region].[Region]) ,[Measures].[Cost] > 1000 ) , [Measures].[Cost2] )
At least you should try an nonempty over the whole tuple. If it is too slow, try to nonempty every single dimension. Proposed as answer by Jerry NeeModerator Tuesday, January 31, 2012 8:43 AM
 Marked as answer by Jeroen Bolle Thursday, February 2, 2012 2:42 PM
All replies

It is possible, but you have to test the performance on you cube. Perharps Aggregations can help but relational it would be the fastest way.
Try this:
with member [Measures].[Cost2] as [Measures].[Cost]1000; create member currentcube.[Measures].[Diff over 1000] as sum( filter( existing ([Dim Product].[Product].[Product]* [Dim Category].[Category].[Category]* [Dim Region].[Region].[Region]) ,[Measures].[Cost] > 1000 ) , [Measures].[Cost2] )
At least you should try an nonempty over the whole tuple. If it is too slow, try to nonempty every single dimension. Proposed as answer by Jerry NeeModerator Tuesday, January 31, 2012 8:43 AM
 Marked as answer by Jeroen Bolle Thursday, February 2, 2012 2:42 PM

Thank you for your example. Performance is indeed a serious issue when this calculation is performed. The product of those three dimensions is too much for my server to handle.
I worked around it by storing in a concatenate of those three keys in my fact table and creating a cube dimension with my fact table as source and that concatenate as the only attribute hierarchy.
Same results with acceptable performance.
Kind regards,
Jeroen