Lookup row information of a related id

[cran]

Hi, 

I have a table that has

- col A: unique work ids,

- col B: users assigned to the Id

- col C: related workid. 

The related work Id also exists in the main unique work ids in column A as depending on the type of work of the Id in Col A it can may have a related id. 

So it looks as below

Col A unique id	col b userid	col c relatedid
1	aa
2	bb	3
3	cc	1
4	dd

How can I add a 4th column that brings through the userid of the relatedid in col C. 

As an example, for unique id 2, it's related id is 3, so I would like to bring the userid against 3 (user id cc) against the row that has unique id 2 

Hope that makes sense happy to provide more information but unfortunately can't provide actual data. 

Thank you 

[cran]

@rubayatyasmin thank you!

[rubayatyasmin]

Hi, @cran 

you can accomplish this using a calculated column and the RELATED function.

Here's the step-by-step process:

1. You first need to create a relationship between col_A_unique_id and col_c_relatedid. In Power BI Desktop, go to "Model" view by clicking on the "Model" icon in the left pane.

2. Now, drag "col_A_unique_id" to "col_c_relatedid" to create a relationship.

3. After creating a relationship, go to "Data" view by clicking on the "Data" icon in the left pane.

4. Select the table and click on "New column" under the "Modeling" tab.

5. In the formula bar, type the following formula and hit Enter:

col_d_related_userid = RELATED(Table1[col_b_userid])

This formula will create a new column named "col_d_related_userid" that pulls the corresponding user id from column B based on the related id in column C. Note that, the RELATED function works only if there's an active direct or indirect relationship between the tables or if col_c_relatedid and col_A_unique_id are in the same table.

Remember that, if you have a row in col_c_relatedid that doesn't correspond to any row in col_A_unique_id, Power BI will return an error. This can be handled by using the IF and ISERROR functions to return a blank or some other value when an error occurs. Here's how you could adjust the formula:

col_d_related_userid = IFERROR(RELATED(Table1[col_b_userid]), BLANK())

This formula will return a blank if the RELATED function returns an error.

[cran]

Hi @rubayatyasmin 

Thank you for this - I should have mentioned both columns are in the same table. I can't create a relationship between 2 columns in the same table. 

Is there another way to calculate this if both columns are in the same table? Of not shoukd I then create a separate calculated table and then do the steps you have outlined above? 

Thank you

[rubayatyasmin · Accepted solution]

@cran 

Use LOOKUPVALUE dax function. 

Example: col_d_related_userid = LOOKUPVALUE(Table1[col_b_userid], Table1[col_A_unique_id], Table1[col_c_relatedid])

document: https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax