Forum Discussion

vickyprudhvi's avatar
vickyprudhvi
Helper IV
10 years ago
Solved

Measure in SSAS 2012

Hi Guys, Sorry for posting this hear . I am not sure where to post this question. Following is a sencario, I need to calculate average medical paymet for each primary body part. But I am not ...
  • austinsense's avatar
    10 years ago

    This is a classic many to many problem (M2M). Your problem is that the relationships only flow from the one side to the many side of the tables. With emoticons ...

     

    Dim Body Part >> Body Part Fact :smileyhappy:

    Body Part Fact >> Dim Claim :smileysad:

    Dim Claim >> Derivation Fact Table :smileyhappy:

     

    You can handle this in Power BI or SSAS 2016 with bi-directional filters(which is much much easier!) - in earlier versions of SSAS you need to take this approach.  It's actually a very simple solution ...

     

    Medical Amt = CALCULATE( SUM( Derivation Fact Table[Medical Amt]), Body Part Fact)
    
    Count Claims = CALCULATE( COUNTROWS( Derivative Fact Table), Body Part Fact)
    
    Average Payment = DIVIDE( [Medical Amt], [Count Claims])
    
    
    

     

    I may not have your metrics exactly right but this should get you started - the solution is to include the table in the middle of the M2M relationship as an argument in a calculate function.

     

    Kudos to you for including a data model diagram.

  • austinsense's avatar
    austinsense
    10 years ago

    vickyprudhvi hey great question - (looking at your diagram) you need the relationship in your report to flow from the right to the left.  If you have a need for the relationship to flow in this direction ...

     

    Derivation Fact Table >> Dim Claim

     

    Then you'll need to include that fact table as a third argument in your calculate function.  I don't see a need for you to do that.  Just give the DAX a try and see if the results are good.