Lookup value from one row and return value from a different row

[Anonymous]

Hello Everyone,

This is my first post so apologies if I am posting this in the wrong place. I am also very new to PowerBI so I will try to explain this to the best of my ability 🙂

Currently I am trying to build a PBI report that uses 2 SQL tables mainly. 

Table 1 has a title column where I have job references and streets, however if you want to find let's say what streets belong to project X, you have a Parentid and cascadeid that connects them. The parentID and cascade are saved separately on table2 which I have been able to lookup them with 2 custom columns on table 1 using the CALCULATE function.

Where I am stuck right now is that I want to create another column that looks at the ParentID, finds the same CascadeID on a different line and returns the title, so I can have both the job ref and street on the same row. 

This is an example:

These streets all have the Parent ID 720266:

And the Job Reference match on the cascadeid is this:

And I want a calculated column that everytime the ParentID check matches the Cascade Check, regardless of the line, returns the Title value of the matched line. It would look a bit like this:

Thanks in advance for all your help

Diogo.

[petrawiggin]

Hello! 

How would this be done if all the data were in the same table? Example:

Key	Year	Vendor	OutwardReference	InwardReference
1234	2020	A	7890
2345	2021	A	6789
6789	2024	A		2345
7890	2024	B		1234
9982	2024	B

I want to look up each OutwardReference in the Key column and then return the year and vendor of the row where it is found. So, the results would look like this:

Key	Year	Status	OutwardReference	InwardReference	LaterYear	LaterStatus
1234	2020	A	7890		2024	B
2345	2021	A	6789		2024	A
6789	2024	A		2345
7890	2024	B		1234
9982	2024	B

What is the best way? Thank you.

SECOND EDIT

The way I figured out to do it is to

1. merge the table against itself
2. expand the columns I want to compare and to show (year and status)
3. create conditional columns that...
   1. IF the expanded year column is null returns a null (where the outward reference doesn't match the key of another row, the new columns shouldn't try to do math and return an error)
   2. Otherwise compares the original year column to the expanded year column from the merge and if the expanded one is later then put in that year as the later year
4. same thing but if the year is later put in that status as the later status
5. remove the expanded columns and just leave the custom ones

Is there a more efficient way to do this?

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

Table1:

Table2:

Create a calculated column in Table1:

Job Reference Check =
LOOKUPVALUE (
    'Table2'[Title],
    'Table2'[Cascade Check], 'Table1'[ParentID Check]
)

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[petrawiggin]

Hello! 

How would this be done if all the data were in the same table? Example:

Key	Year	Vendor	OutwardReference	InwardReference
1234	2020	A	7890
2345	2021	A	6789
6789	2024	A		2345
7890	2024	B		1234
9982	2024	B
1001	2020	A	2002
2002	2020	A		1001

I want to look up each OutwardReference in the Key column and then return the year and vendor of the row where it is found.

EDIT

Actually, I realize I also need an IF statement in there: I only want the later year and later status IFF the later year is different from the year. (This is data from JIRA and I only want to know about the status of a cloned ticket if it was cloned into a project with a different year, not if it was cloned into a project in the same year it was created itself.)

So, the results would look like this:

Key	Year	Status	OutwardReference	InwardReference	LaterYear	LaterStatus
1234	2020	A	7890		2024	B
2345	2021	A	6789		2024	A
6789	2024	A		2345
7890	2024	B		1234
9982	2024	B
1001	2020	A	2002
2002	2020	A		1001

What is the best way? Thank you.

[lbendlin]

Welcome to Power BI and to the forums. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523