Forum Discussion
Lookup value from one row and return value from a different row
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 ...
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
petrawiggin
Helper I
Helper IHello!
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
- merge the table against itself
- expand the columns I want to compare and to show (year and status)
- create conditional columns that...
- 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)
- 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
- same thing but if the year is later put in that status as the later status
- remove the expanded columns and just leave the custom ones
Is there a more efficient way to do this?