Forum Discussion
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:
| Product | Size | Price |
| A | 100 | 10 |
| A | 200 | 20 |
| B | 100 | 40 |
| B | 200 | 80 |
| B | 300 | 120 |
Table 2:
| Product | Color |
| A | Red |
| A | Black |
| B | White |
| B | Blue |
Table 3:
| Product | Size | Price | Color |
| A | 100 | 10 | Red |
| A | 200 | 20 | Red |
| A | 100 | 10 | Black |
| A | 200 | 20 | Black |
| B | 100 | 40 | White |
| B | 200 | 80 | White |
| B | 300 | 120 | White |
| B | 100 | 40 | Blue |
| B | 200 | 80 | Blue |
| B | 300 | 120 | Blue |
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
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
Hope it is clear!!!
Regards,
Thejeswar
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
6 Replies
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
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
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
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
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.
