MDX Calculated Measure.

Question

This is rather frustrating as i'm trying to convert easily defined measures in DAX tabular cube into an MDX cube due to reasons at work.

The DAX equivalent is

CREATE MEASURE 'VALUES'[Total Attributable Costs]=CALCULATE(sum([VALUE]),left('PL'[PLCODE],3) = "ATR");

I'm trying to achieve this in MDX expression... the following fails.

SUM({left([PL].[PL Code].currentmember.name,3)="ATR"},[Measures].[Value])

I'm totally new to this. please help !

Answer 1

Hi PentAgraMm,

Thanks for your question.

In this scenario, you can create a named calculation called "Attributable Cost" in data source view table of your SSAS project:

Using following logic:
CASE
    WHEN Left([PL Code],3)="ATR") 
    THEN value
    ELSE NULL
END

After that, you can create a measure called [Total Attributable Costs] using aggregate function SUM based on this named calculation "Attributable Cost".

---

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 2

For SSAS MD, it might be a good idea to set all the necessary groupings as attributes in the PL dimension. E.g. you could define [PL lvl 3] as Left(PLCode,3) and then use a simple ( [Measures].[Value], [PL].[PL lvl 3].&[ATR] ) tuple to return the pre-computed value from the cube.

The MDX for your case would be (provided the Name property does start with 'ATR' where appropriate)

SUM ( [PL].[PL Code].[PL Code]
      , IIF ( VBA!LEFT([PL].[PL Code].CurrentMember.Name,3)='ATR'
              ,[Measures].[Value]
              ,NULL
             )
    )

---

Expect me to help you solve your problems, not to solve your problems for you.

Answer 3

This worked beautifully.

Thank you so much, you're a lifesaver. I was quite stuck on this being new to MDX and all the complexities of MD cubes.

Answer 4

Thank you for your reply.

This would work for a scenario where I only have one measure to cater for. I have quite a lot and creating calculated fields for every scenario would be a bit too cumbersome. However I learnt of alternate possibilities through your reply and I thank you for that. much appreciated. :-)