Forum Discussion
Help with multiple Power BI relations between the same table
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?
Hi MikeSwe
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
2 Replies
Hi MikeSwe
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
Thank you very much, it worked perfectly.
