Many to many join

[Anonymous]

Hello experts

I generally avoid many to many join that’s why I joined my >30 dim tables with fact tables using many to one join at first.
Now that model is not retrieving data from some dim tables and looks like when I used one of the key dim tables as middle layer and connected all dim tables to this key dim table using many to many and then key dim table to a fact table using many to one, data is coming together nicely in table view.
I am building self service report for users. The thing I am not sure about what kinds of problems this many to many join could lead to for users when they will start building own report and how to minimize that
Thanks

[v-yingjl · Accepted solution]

Hi @Anonymous ,

For this case, we do not recommend use many to many relationship in data model, usually create many-one-many relationships by a transitionary dim table instead as you said.

You can refer the following Microsoft documents that help you:

1. https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships
2. https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

Best Regards,

Yingjie Li

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

[Greg_Deckler]

Potentially many problems. Sorry bad joke. Hard to say definitely without knowing more about your data model and how this many-to-many is being used.

[Anonymous]

Good one Greg.
I was curious to know about potential problems with many to many join. I connected my dim tables with a key dim table using many to many and then key dim table with a fact table using many to one and then fact table to other fact tables using one to one,
Any red flag?