Forum Discussion
Two Associations to the Same Table
I have two associations to a "user" table for two different columns in a "project" table. I am told I need to have a different instance of "user" in order to join to a different column in the "project" table. I am told there needs to be two different instances of User. If this were SQL, this is how it would look:
SELECT
u1.first_name as created_by_name,
u2.first_name as modified_by_name
from project_pivot pv
join user u1 on u1.id [u1.id] = pv.created_by
left join user u2 on u2.id [u2.id] = pv.modified_by
.....
.....
I am not sure how this should be done in PowerBI.
4 Replies
Anonymous can you post a sample data/pbix with the expected output?
I have a dashboard showing all users who create projects in a ticketing system, however, not all users create their own projects. I have a dashboard to show the time spent by all users on projects (modified by). So I have two scenarios and need two relationships (created by and modified by) but both need to point to the same "user" table. To achieve this, can I use PowerBI to duplicate the user table (user2), so I can then have both scenarios to create both dashboards? What is the best way to achieve this, and how can I keep table 2 updated with table 1.
Hi Anonymous ,
If you have a relationship between ttwo tables, you can use RELATED function . If they don't have a relationship. So try to use LOOKUPVALUE.
Information about these two fuctions below
https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax
https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I'm not sure this is the solution for what I am trying to do. I have a table that has a list of users (Users). I have a 2nd table that has a list of projects (Projects). I already have one relationship joining 'user.id' from the Users table, with 'created_by' from the Project table. I now need to create a relationship using these same two tables but this time, I need to join 'user.id' from the User table to 'modify_by' from Project table.
Can I have two relationships with the same two tables?
