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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

Two joins between 2 tables

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 ?

 

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

dshmulenson
New Member

Hi

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

View solution in original post

v-binbinyu-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
josef78
Memorable Member
Memorable Member

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.

v-binbinyu-msft
Community Support
Community Support

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.

dshmulenson
New Member

Hi

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

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors