Help with multiple Power BI relations between the same table

[MikeSwe]

Hi,

I am completely new to Power BI and after several days of searching online and experementing I have not been able to solve this and turn to this community for help.

I have a SharePoint list containing user information, e.g.

UserID	UserName
10	Person A
20	Person B
30	Person C

Another SharePoint list containing information of various projects, e.g.

ProjectName	Description	ProjectManagerID	ProjectLeadID
Project 1	Text...	10	30
Project 2	Text...	20	30
Project 3	Text...	20	20

In the Power BI report that I am trying to create I need to display the name of both the ProjectManager and the ProjectLead.

By using a relationship between the two lists I have managed to display the name of the Project Manager for each project when selecting it with the slicing tool. However, since I cannot have two active relationships between the two tables at the same time I have created an inactive relationship that I try to use in a measure with the help of USERELATIONSHIP but cannot get this to work.

Would anyone know the correct syntax for CALCULATE and USERELATIONSHIP to get both the name of the Project Manager and the Project Lead? Alternatively if there is another and better way to achieve the same?

[PhilipTreacy · Accepted solution]

Hi @MikeSwe 

Download sample PBIX file

You can create 2 measures to return the names

Project Lead = CALCULATE(MAX('Users'[UserName]), FILTER('Users', SELECTEDVALUE(Projects[ProjectLeadID]) = 'Users'[UserID]))

Project Manager = CALCULATE(MAX('Users'[UserName]), FILTER('Users', SELECTEDVALUE(Projects[ProjectManagerID]) = 'Users'[UserID]))

Regards

Phil

[MikeSwe]

Thank you very much, it worked perfectly.