topic Re: one to many relationship RELATED () function in DAX Commands and Tips

one to many relationship RELATED () function

georgec96 — Wed, 10 Aug 2022 07:44:10 GMT

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.

Re: one to many relationship RELATED () function

johnt75 — Wed, 10 Aug 2022 08:15:29 GMT

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])

Re: one to many relationship RELATED () function

georgec96 — Wed, 10 Aug 2022 08:19:51 GMT

johnt75

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?

Re: one to many relationship RELATED () function

johnt75 — Wed, 10 Aug 2022 08:24:42 GMT

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

Re: one to many relationship RELATED () function

georgec96 — Mon, 15 Aug 2022 13:29:39 GMT

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

Re: one to many relationship RELATED () function

johnt75 — Mon, 15 Aug 2022 13:56:58 GMT

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] )

Re: one to many relationship RELATED () function

georgec96 — Mon, 22 Aug 2022 06:45:51 GMT

johnt75 Thank you