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 :)
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.
Microsoft EmployeeThe 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 :)