topic Re: Two joins between 2 tables in Report Server

Two joins between 2 tables

prajaktakeni11 — Wed, 21 Feb 2024 14:21:36 GMT

Hello I am new to Power BI , Wanted to ask can I have two relation between two table on different columns
For Example

1st Relation --TableA(Emp ID)-TableB(Emp ID)

2nd Relation --TableA(Service)-TableB(Service)

Ist this valid in Power BI ?

Re: Two joins between 2 tables

Greg_Deckler — Wed, 21 Feb 2024 14:52:55 GMT

prajaktakeni11 Yes, but one relationships will be inactive. You can use USERELATIONSHIP to select the relationship you want to use in a calculation in DAX.

Re: Two joins between 2 tables

dshmulenson — Wed, 21 Feb 2024 14:57:36 GMT

yes it is possible. but you probaly have to active one of the relationships

Re: Two joins between 2 tables

Anonymous — Thu, 22 Feb 2024 05:46:24 GMT

Hi prajaktakeni11 ,

First of all, many thanks to dshmulenson and Greg_Deckler for your very quick and effective replies, and I will give some additions below:

To answer your question: Yes, you can indeed have multiple relationships between two tables in Power BI, including on different columns as you've described. You can have a relationship between 'TableA(Emp ID)' and 'TableB(Emp ID)'as well as between 'TableA(Service)' and 'TableB(Service)'. This is a valid scenario in Power BI.

However, it's important to note that while you can define multiple relationships between two tables, only one of these relationships can be active at any given time. The active relationship is the one that Power BI uses for filter propagation in your reports by default. The additional relationships will be set as inactive but can be utilized in your DAX calculations using the 'USERELATIONSHIP' function to temporarily treat an inactive relationship as active.

For more detailed guidance on creating and managing relationships in Power BI, including setting relationships as active or inactive, I recommend checking out the following documentation: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

And for utilizing inactive relationships in your DAX calculations: USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Two joins between 2 tables

josef78 — Sun, 25 Feb 2024 16:21:48 GMT

Depends on what you want...

If you need two relations between two tables, it is possible (only one relation can be activelly used).

If you need single relation on two pair of columns beween two tables, it is not possible directly, you must make merge column first.