Forum Discussion
Table with Multiple User Unique ID's to Lookup Table
Hey guys so I have a product table that I need to connect to a User table because the product table will say the person's ID not their name. But the catch is that the product table has multiple users in it. Currently, I am just duplicating the User table as many times as I need it.
Example:
Product Table:
| Product ID | User | Team Lead | Manager |
2 | asb1 | asb2 | asb3 |
| 3 | asbh | asbh2 | asbh3 |
User Table:
| User: | Name: |
| asb1 | Devin Howard |
| asbh | Johnny Smith |
| asb2 | Howard Levin |
I cant create multiple connections to the user table off User, Team Lead, Manager because Power BI automatically makes any multiple inactive. I am wondering if there is a way to basically mask all of their User ID's?
Hi,
I think the first table should be transformed into a 3 column Table - Product ID, Attribute and Value. The Attribute will have User, Team Lead and Manager and the value column will have the user names. This can be done in the Query Editor using the "Unpivot Other columns" feature. Then you can build a relationship from the Value column of Table1 to the User column of Table2.
Hope this helps.
Hi Anonymous
Besides of Ashish_Mathur 's suggestion,
Leave the tables no relationship, create measures/columns in "product"table,
Measure_user = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[User])) Measure_team = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[Team Lead])) Measure_manager = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[Manager])) Column_user = LOOKUPVALUE(Table2[Name],Table2[User],Table1[User])Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
Here is an example of Ashish_Mathur's suggestion:
Unpivot columns "User","team leader","manager" in Edit queries,
close&&apply, create relationnships,
add column from two tables in a matrix.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous
Besides of Ashish_Mathur 's suggestion,
Leave the tables no relationship, create measures/columns in "product"table,
Measure_user = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[User])) Measure_team = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[Team Lead])) Measure_manager = LOOKUPVALUE(Table2[Name],Table2[User],MAX(Table1[Manager])) Column_user = LOOKUPVALUE(Table2[Name],Table2[User],Table1[User])Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
Here is an example of Ashish_Mathur's suggestion:
Unpivot columns "User","team leader","manager" in Edit queries,
close&&apply, create relationnships,
add column from two tables in a matrix.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
I think the first table should be transformed into a 3 column Table - Product ID, Attribute and Value. The Attribute will have User, Team Lead and Manager and the value column will have the user names. This can be done in the Query Editor using the "Unpivot Other columns" feature. Then you can build a relationship from the Value column of Table1 to the User column of Table2.
Hope this helps.
Hi,
You are welcome. The number of column on the first table does not matter. We are carrying out the transformation on the first table.
Ashish_Mathur v-juanli-msft Thank you all so much! I just got back to this at work thank you it worked for me 🙂 I used the column idea because it was quick but they all work.
You are welcome.