Table with Multiple User Unique ID's to Lookup Table

[Anonymous]

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?

[Ashish_Mathur · Accepted 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.

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[Anonymous]

@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.

[Ashish_Mathur]

You are welcome.

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[Ashish_Mathur · Accepted 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.

[Anonymous]

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. 

[Ashish_Mathur]

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.