Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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_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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou 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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |