DAX Lookup value in the same table

It looks like you want to assign the "Assignee" value to each row in your table based on certain conditions. To achieve this, you can use DAX measures and a combination of functions to perform the lookup and assignment. Here's a modified version of your DAX code to achieve the desired result:

Assigned =
VAR Key =
Table[ID]
VAR LatestStatusDate =
CALCULATE(
MAX(Table[EndDate]),
FILTER(Table, Table[ID] = Key)
)
VAR LatestAssignee =
CALCULATE(
MAX(Table[Assignee]),
FILTER(
Table,
Table[ID] = Key &&
Table[AssigneeEndDate] <= LatestStatusDate
)
)
RETURN
LatestAssignee

This code should assign the appropriate "Assignee" value to each row based on the conditions you specified. The key points in this code are:

1. We use a VAR statement to define the "Key" and "LatestStatusDate" variables.
2. For "Key," we simply reference the "ID" column from the table.
3. For "LatestStatusDate," we use the CALCULATE function with a FILTER condition to find the maximum "EndDate" value for rows where the "ID" matches the current row's "ID."
4. In the "LatestAssignee" variable, we use CALCULATE with another FILTER condition to find the maximum "Assignee" value for rows where the "ID" matches the current row's "ID" and the "AssigneeEndDate" is less than or equal to the "LatestStatusDate."
5. Finally, we return the "LatestAssignee" value, which will be assigned to each row in your table.

When you use this measure in your table, it should give you the desired result, as shown in your "Result" section.

Hi  AC2023 ,

Here are the steps you can follow：

1. Create measure.

Measure =
MAXX(
    FILTER(ALL('Table'),
    'Table'[EndDate] in SELECTCOLUMNS('Table',"1",[AssigneeEndDate])),[StartDate])

Measure 2 =
MAXX(
    FILTER(ALL('Table'),
    [Measure]=MAX('Table'[StartDate])),[Assignee])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly