Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chirag_Sidana
Frequent Visitor

Many to Many Join in Power Bi

Hi,

 

I am trying to join two tables which have a many to many relationship between them. For example, look at the tables below:

Table 1:

 

ProductSizePrice
A10010
A20020
B10040
B20080
B300120

 

Table 2:

ProductColor
ARed
ABlack
BWhite 
BBlue

 

Table 3:

ProductSizePriceColor
A10010Red
A20020Red
A10010Black
A20020Black
B10040White
B20080White
B300120White
B10040Blue
B20080Blue
B300120Blue

 

I want to perform a left join on table 1 with table 2 but the issue is that the common variable (product) has a many to many relationship in both tables. I want the output to look like table 3. I read about bridge tables but couldn't quite understand how to apply them in this scenario. Can anyone please demonstrate the solution to this join in powerBI?

 

Thanks in advance,

Chirag

2 ACCEPTED SOLUTIONS
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @Chirag_Sidana,

In the below picture showing the relationships, I have 2 tables which are joined by Data. But here the problem is both Table1 and Table2 have Multiple entries of same Dates resulting in a many-to-many relationship

 

To handle this, a new table Table1 (2) is introduced. This is the bridge table in this relationship. This table will have distinct entries of Dates from one of the tables. (Usually the table which has more dates in it). using this you can now connect Table1 with Table2

 

relationship.PNG

 Hope it is clear!!!

 

Regards,

Thejeswar

 

 

View solution in original post

MFelix
Super User
Super User

Hi @Chirag_Sidana,

 

The solution provided by @Thejeswar is correct, however is a smal catch that is the details of the second table you need to active the croos filter in both sides of the relationship.

 

Check the PBIX file in attach.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
Chirag_Sidana
Frequent Visitor

Thanks @Thejeswar and @MFelix! Your solution does seem to work on the example I provided above and even though I have replicated it, unfortunately, it is not working on my actual dataset. I am trying to understand what the issue could be. Many thanks for your support! Will surely let you know once I am able to rectify the error.

 

Regards,

Chirag

Hi @chirag, were you able to solve this? Even am facing issues with many to many relationship.
MFelix
Super User
Super User

Hi @Chirag_Sidana,

 

The solution provided by @Thejeswar is correct, however is a smal catch that is the details of the second table you need to active the croos filter in both sides of the relationship.

 

Check the PBIX file in attach.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@Chirag_Sidana,

You can use the file shared by @MFelix.

 

You can use bi-directional cross filtering depending on your need.

In your case it is with the second table.

 

Regards,

Thejeswar

Thejeswar
Resident Rockstar
Resident Rockstar

Hi @Chirag_Sidana,

In the below picture showing the relationships, I have 2 tables which are joined by Data. But here the problem is both Table1 and Table2 have Multiple entries of same Dates resulting in a many-to-many relationship

 

To handle this, a new table Table1 (2) is introduced. This is the bridge table in this relationship. This table will have distinct entries of Dates from one of the tables. (Usually the table which has more dates in it). using this you can now connect Table1 with Table2

 

relationship.PNG

 Hope it is clear!!!

 

Regards,

Thejeswar

 

 

Hi Thejeswar,

 

Thanks for the prompt response. I have created the bridge table as demonstrated but how do I now reach the end result?

 

Regards,

Chirag

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.