Multiple Relationships

[cpowers]

Hello All,

I'm sure this question has been posed before but I'm new to Power Bi and don't know exactly how to ask.

I have an Opportunities table that references Users, from User table, for multiple fields (Owner, Pursuit Leader, Admin User). I created the relationship between Opportunities Owner ID and User ID. This allows me to show Opportunity values based on the Full Name of the User Table. However, I also need to be able to show the Pursuit Leader and Admin User of those Opportunities. 

I am aware that you can only have one active relationship. How can I show Owner, Pursuit Leader, and Admin User names all at the same time with only the single relationship.

Thank you for your help!

[BetterCallFrank · Accepted solution]

Hi @cpowers

please create the additional relationships and leave them inactive.

Then create measures like this:

Admin User = CALCULATE(
  VALUES(Users[Full Name]),
  USERELATIONSHIP(Opportunities[Admin User ID], Users[User ID])
)

PS: see https://www.sqlbi.com/articles/userelationship-in-calculated-columns/ for other concepts around this topic and additional patterns/solutions for your question - maybe consider the VLOOKUP solution here

HTH,

Frank