Table with 2 fields referencing the same field in an other Table

[iggys76]

Hi all!

I have a Table

Discussions

Id user1 user2

and an other Table

Users

Id Name Surname

Now both the fields user1 and user2 in Discussions refer to the same Id field in Users but I cannot create a relationship between Discussions.user2 and Users.Id since there already is a relationship between Discussions.User1 and Users.Id and Power BI says I need to deactivate that relationship to be able to create a new one between Discussions.User2 and Users.Id.

I want to be able to display both the Users having the Discussion so I need to be able to link both Discussions.User1 and Discussions.User2 to Users.Id to display Users.Name & Users.Surname for both Users in the report... how would I do that?

Thanks.

[edhans · Accepted solution]

Best option is in the source data using Power Query is to concatenate User1 and User2 in Discussion table into a single field using the Merge Column feature. Then do the same for the Users table for Name and Surname fields.

Then do your join with the merged fields from both tables.

If you cannot do in the source data or with Power Query, do it in the table view with either the CONCATENATE() function, or using the ampersand. Then do the join with those merged fields.