Answered by:
MDX Calculated Measure.

-
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 !
Question
Answers
-
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.
- Proposed as answer by alexander fun Tuesday, March 21, 2017 9:30 AM
- Marked as answer by PentAgraMm Wednesday, March 22, 2017 10:33 PM
All replies
-
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 MSDNFSF@microsoft.com- Proposed as answer by alexander fun Tuesday, March 21, 2017 9:30 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 ) )
Expect me to help you solve your problems, not to solve your problems for you.
- Proposed as answer by alexander fun Tuesday, March 21, 2017 9:30 AM
- Marked as answer by PentAgraMm Wednesday, March 22, 2017 10:33 PM
-
-
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. :-)