# 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])

Tuesday, March 21, 2017 5:03 AM

• 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
)
)```

• Proposed as answer by Tuesday, March 21, 2017 9:30 AM
• Marked as answer by Wednesday, March 22, 2017 10:33 PM
Tuesday, March 21, 2017 9:22 AM

### All replies

• Hi PentAgraMm,

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 MSDNFSF@microsoft.com

• Proposed as answer by Tuesday, March 21, 2017 9:30 AM
Tuesday, March 21, 2017 5:37 AM
• 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
)
)```

• Proposed as answer by Tuesday, March 21, 2017 9:30 AM
• Marked as answer by Wednesday, March 22, 2017 10:33 PM
Tuesday, March 21, 2017 9:22 AM
• ```SUM ( [PL].[PL Code].[PL Code]
, IIF ( VBA!LEFT([PL].[PL Code].CurrentMember.Name,3)='ATR'
,[Measures].[Value]
,NULL
)
)```
Tuesday, March 21, 2017 9:33 AM
• 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.

Wednesday, March 22, 2017 10:33 PM