one to many relationship RELATED () function

[georgec96]

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.

[johnt75]

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

[georgec96]

@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?

[johnt75]

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

[georgec96]

@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

[johnt75 · Accepted 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]
)

[georgec96]

@johnt75 Thank you