Replace values with values in other table

[nikkie]

Hi guys,

This is probably quite an easy one for you, but I can't get my head around something. Hope you can help.

Some background information. We use power bi to visualize data captured by our data collection tool for quality management and inspections. I have a table with all the inspections that are done for a project. Containing the name of the inspection etc, but also who added the inspection and who closed it. The user who added the inspection and who closed it are specified by User ID in that table. In the table Users, I have the User ID and a fullname.

Because you can only have one link between the two tables to connect the User ID to the User ID in both tables, I can't get a fullname in my visualisation like that for the user who added the inspection AND for the one who closed it.

I know that it's possible to automatically show the Full name for the "add user ID" and the "closed user ID", but I don't know how.

Can you help me?

[SabineOussi]

If I understood your case correctly, you have table1 and table2 both with linked user IDs and you want to show the full name from table2?

If that's the case, you can choose whatever column you want to be shown in your visualization even if it's not the one relating the two tables. Choose your visual and drag the full name, what does it give you?

[nikkie]

Hi Sabine,

No that's the whole problem. I will try to explain some more.

I have table 1 with:

Inspection name | User who added inspection (ID) | User who closed inspection (ID)

Inspection x        | 5                                                |  8

Inspection y       | 4                                                |  5

And table 2:

User ID | Fullname

4          | John

5          | James

8          | Mark

Normally if you have one link for User ID it can be done like you say. That's easy. But I want to show them both as a fullname so that I can get:

Inspection name | User who added inspection | User who closed inspection

inspection x        |James                                   | Mark

Inspection y        |John                                     | James

[SabineOussi]

My quick solution would be to duplicate table2 and have two different relationships:

1- User who added inspection (ID) with User ID from table2

2- User who closed inspection (ID) with User ID from table2 (2)

When you create your visual, use Fullname for added inspection from table2 and Fullname for closed inspection from table2 (2), remove the blank inspections.

Rename your table accordingly for clarity.

If someone has a better solution, let us know.

Hope this helps for now.

[nikkie]

Hi Sabine,

Ah yeah thanks. I will give that a try. If there is another possibility, I'm happy to hear that too.

[v-sihou-msft]

@nikkie

In your scenario, since you want to reference name column in table 1 based on ID column, you can use LOOKUPVALUE() function to include the name column in your Table 1 dataset. Then you can directly drag the name field in to your visual. The expression can be like:

Name = LOOKUPVALUE(Table2[NAME],Table2[id],Table1[id])

Regards,

[nikkie]

Great! Thanks! I'm going to try this right away, but I was thinking in that direction, but didn't know what code to use!