Many to mAny relationship

[poweruser999]

Hi All,

Why doesn't power BI allow Many to Many relationships?

What happens in the background. Why cant we do it here when we can do the SAME thing in SQL SERVER. (As I am Assuming this when create a join it will create a sql on background.)

Please explain to me as I am a newbie.

Thanks

[PerrigoNVanLent]

I didn't see it listed in the feature summary (https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-2018-feature-summary/), but it looks like the "Many to Many" option (when creating a table join) is now available in the July update.

[Phil_Seamark]

HI @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?

[poweruser999]

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 · Accepted solution]

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 🙂