none
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 !


    Tuesday, March 21, 2017 5:03 AM

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
    Tuesday, March 21, 2017 9:22 AM

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


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

    Wednesday, March 22, 2017 10:35 PM