Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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])
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.
@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.
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.
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])
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.
Thank you for your reply!
I don't think this will work because there are like 100 other columns in the user table and I have to sum some of those columns.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |