Cardinality issue with Many to Many Relationship

[Dc123]

Hello,

I am currently having cardinality issues with a many to many relationship in Power BI. I have used opportunity number as the relationship, but now that i have done that it is messing up with my data.

Would anyone be able to help me with this issue?

thanks

[lbendlin]

Welcome to the club.  Opportunity number or Sales Order Number or Order datetime are prime examples for what not to use as a join key .  Their cardinality is just too high.  For reference - the magic line is somewhere around 50k  (yes, k, not M !!!)

Your best option is to not need that relationship. See if you can use TREATAS instead.  Another option would be to pre-multiply the tables and thus eliminate the join.  But that may not be possible if you don't have enough memory/compute.

Relationships in Power BI and Tabular models - SQLBI

Different options to model many-to-many relationships in Power BI and Tabular - SQLBI