Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have two tables with and trying to get the related column from the many side of the relationship to the one side, however the function RELATED() does not work.
I have a table called open_orders and one table called open_lines which contains duplicate values, I would like to get the status column from the open_lines and bring it to the open_orders table.
Could anyone explain me how could i achieve that?
Thank you.
Solved! Go to Solution.
You would need a column containing unique identifiers in case of multiple entries on the same date. If there isn't such a column in the data already, you could add an index column in Power Query and use that.
Then you can create a column like
Status =
SELECTCOLUMNS (
TOPN (
1,
RELATEDTABLE ( order_lines ),
order_lines[status date], DESC,
order_lines[unique ID], DESC
),
"@val", order_lines[Status]
)
RELATED works from the many side to the one side, you need RELATEDTABLE. You could create a column like
Status = MINX( RELATEDTABLE(open_lines), open_lines[status])
Thank you, that worked perfectly. Would you be able to explain how does it work though? If there is duplicate values which one is it retrieving?
It will get the first one in alphabetical order, that is what the MIN is doing. From the screenshots it looked like all entries for the same order had the same status, if that's not the case then you could look to combine SELECTCOLUMNS, TOPN and RELATEDTABLE to sort the related entries by whichever columns were relevant
@johnt75 Would it be possible to retrieve the value from the status column based on another date column?
Basically I want to get the latest status based on the date column
You would need a column containing unique identifiers in case of multiple entries on the same date. If there isn't such a column in the data already, you could add an index column in Power Query and use that.
Then you can create a column like
Status =
SELECTCOLUMNS (
TOPN (
1,
RELATEDTABLE ( order_lines ),
order_lines[status date], DESC,
order_lines[unique ID], DESC
),
"@val", order_lines[Status]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |