Forum Discussion
Multiple connections between 2 tables
Hello,
I am desperately trying to make multiple connections between different tables. Let me explain:
I have 2 tables with dates and references that I want to join together. These tables are connected through a date table (calendar). Power BI indicates that I can only make one connection, otherwise there will be "ambiguity". Is there a solution to connect multiple columns between the files?
The goal is to be able to link the dates and references together to retrieve information from the second table.
Thank you very much for your help
Hi QCunity
We can build multiple relationships between two tables, but only one relationship can be active, the other relationships will be inactive. With USERELATIONSHIP function, we can make an inactive relationship active and make it affect the calculation of a DAX formula where the function is used. Only one relationship can be active at a time.
Active vs inactive relationship guidance - Power BI | Microsoft Learn
In your scenario, one approach is to add a dimension table for Reference, just like your date table. Then connect this Reference table to those two tables.
Another approach is to use Merge queries feature in Power Query. If your purpose is to bring matching data from one table into the other table based on two columns, Merge queries feature allows you to select one or more columns as matching columns at the same time, and bring matching data into the first query or into a new query. You can choose to use different types of joins, depending on the output you want. Merge queries overview - Power Query | Microsoft Learn
Hope this will be helpful!
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
2 Replies
Hi QCunity
We can build multiple relationships between two tables, but only one relationship can be active, the other relationships will be inactive. With USERELATIONSHIP function, we can make an inactive relationship active and make it affect the calculation of a DAX formula where the function is used. Only one relationship can be active at a time.
Active vs inactive relationship guidance - Power BI | Microsoft Learn
In your scenario, one approach is to add a dimension table for Reference, just like your date table. Then connect this Reference table to those two tables.
Another approach is to use Merge queries feature in Power Query. If your purpose is to bring matching data from one table into the other table based on two columns, Merge queries feature allows you to select one or more columns as matching columns at the same time, and bring matching data into the first query or into a new query. You can choose to use different types of joins, depending on the output you want. Merge queries overview - Power Query | Microsoft Learn
Hope this will be helpful!
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Yes, that's very hepful !! thank you so much 😁
