Measure in SSAS 2012

[vickyprudhvi]

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 able to create correct measure using dax. Kindly help me out

[austinsense · Accepted solution]

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

Body Part Fact >> Dim Claim

Dim Claim >> Derivation Fact Table

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 · Accepted solution]

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

Body Part Fact >> Dim Claim

Dim Claim >> Derivation Fact Table

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.

[vickyprudhvi]

@austinsense - thank you for ur reply. I still have a doubt. u said we need to include middle table of M2M but there are 2 tables  Claim and Body part fact. then why are you including only bodypart fact table.

[austinsense · Accepted solution]

@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.