cancel
Showing results for
Did you mean:
Helper IV

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

2 ACCEPTED SOLUTIONS
Impactful Individual

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.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Impactful Individual

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
3 REPLIES 3
Impactful Individual

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.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Helper IV

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

Impactful Individual

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors