Forum Discussion
Many to mAny relationship
The main reason is often people create these scenarios because they exist in the source system, so they blindly replicate the structures in the data modelling engine.
The primary purpose of a BI data model is to support reporting and analytics - and if you optimise your data structures for this scenario, you find you no longer need many-to-many relationships.
A purist would only use a relationship for filtering and not for cross-table calculations. Just don't be afraid of data duplication through multiple FACT tables :)
Microsoft EmployeeHI poweruser999
Power BI does support it in a roundabout way. Perhaps create an intermediary bridge table that includes the columns you would like to match up.
Does that make sense?
Yes I have seen many people create a unique value bridge and then joining it.
I was just wondering why doesnt it. I am under the assumption, that when ever we create a relationship, on the back end it creates a join like in sql and process it. If this is the case Sql supports many to many why doesnt power BI.
Or Is my assumption that power bi creates a sql query on back end is wrong.
- Phil_Seamark
The main reason is often people create these scenarios because they exist in the source system, so they blindly replicate the structures in the data modelling engine.
The primary purpose of a BI data model is to support reporting and analytics - and if you optimise your data structures for this scenario, you find you no longer need many-to-many relationships.
A purist would only use a relationship for filtering and not for cross-table calculations. Just don't be afraid of data duplication through multiple FACT tables :)