Forum Discussion

georgec96's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago
Solved

one to many relationship RELATED () function

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 ...
  • johnt75's avatar
    johnt75
    4 years ago

    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's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago

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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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