Incorrect total in many to many relationship

[brian0782]

Hi, I have the following data model where there is a many to many relationship between Binding Authority and Claim Amounts:

I have the following measure:

Authority Amt = SUMX(VALUES('Binding Authority'[UMR]),CALCULATE(SUM('Authority Amounts'[Authority Amount])))

The issue I'm having is I'm getting repeating values when I return the value in a matrix:

I think the issue is with Class because when I remove it, it seems to work ok

Any idea where I may be going wrong? 

Many Thanks

[v-angzheng-msft]

Hi, @brian0782 

The current model seems to be more elegant than the previous one, and if it works for you, it's what you need.

Simple is usually good.

If you still want to learn more about data modeling practices, here are related blogs that can help you

https://community.powerbi.com/t5/Community-Blog/Data-Modelling-In-Power-BI-Helpful-Tips-amp-Best-Pra...

https://blog.enterprisedna.co/data-modelling-in-power-bi-tips-best-practices/

https://sqlserverbi.blog/2020/12/25/doing-power-bi-the-right-way-6-data-modeling-essentials-best-pra...

https://www.youtube.com/watch?v=kiVXI7zjSzY

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01 · Accepted solution]

@brian0782  DAX101 says never to use many to many relationship, use a bridging table and build a star schema.

[brian0782]

Hi, thanks for your response.

I did originally have a bridge table but couldn't get it working. 

I've amended the model and I think it's working as expected now.

Is the below model the preferred approach?

[Farouk_Ragab]

Excuse me @brian0782  , Did You get an answer for that problem .

I have the same Issue in subtotals Although I made a bridge instead many-to-many ,I don't use meausures in this case.