# [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,

Thursday, January 26, 2012 4:29 PM

• 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.

Thursday, January 26, 2012 8:50 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.

Thursday, January 26, 2012 8:50 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

Thursday, February 2, 2012 2:42 PM