Forum Discussion
Two connections between two tables
Hi,
I am new to Power BI und need your help.
My source are two tables, 'Issues' and 'Users':
I would ike to come to the following result table:
Therefore I would need two n:1 relations.
The first between Issues[Created_by] and Users[ID] and the second between Issues[Assigned_to] and Users[ID].
Power BI tells me that one one connection ist allowed. How can i solve this?
Regards Andreas
You can only have 1 relationship active at a time, so if you create a relationship based on the created by column you can add 2 new columns to the issues table like
Created by Name = RELATED(Users[Name]) Assigned to Name = LOOKUPVALUE( Users[Name], Users[ID], Issues[Assigned to])If you create the relationship between the Issue table and user table through the Created by field, you can use that directly in the visual (in my example the field is seen as "Name"(you can either change the field's name in the Users table to "Created by" or change the actual name for the visual by right-clicking on the field in the visual's bucket and rename it). For the "Assigned to", you can create a measure along the lines of:
Assigned measure = LOOKUPVALUE ( 'Users Table'[Name], 'Users Table'[ID], MAX ( 'Issues Table'[Assigned to] ) )
4 Replies
This is called role-playing dimensions which basically is using one dimension for creating multiple relationships to your fact table.
One possible approach is to duplicate the Users table and one would connect to the created by and the other one to the assigned by.
I'll leave here this article that might help and has different approaches: https://data-mozart.com/welcome-to-powerbi-thetare-role-playing-dimensions/
You can only have 1 relationship active at a time, so if you create a relationship based on the created by column you can add 2 new columns to the issues table like
Created by Name = RELATED(Users[Name]) Assigned to Name = LOOKUPVALUE( Users[Name], Users[ID], Issues[Assigned to])Thank you all. The LOOKUPVALUE did it.
If you create the relationship between the Issue table and user table through the Created by field, you can use that directly in the visual (in my example the field is seen as "Name"(you can either change the field's name in the Users table to "Created by" or change the actual name for the visual by right-clicking on the field in the visual's bucket and rename it). For the "Assigned to", you can create a measure along the lines of:
Assigned measure = LOOKUPVALUE ( 'Users Table'[Name], 'Users Table'[ID], MAX ( 'Issues Table'[Assigned to] ) )
