Updating data in a table based on most recent data from a different table

[ScottB10]

I have an Assignments table with a TaskID and ReviewerName column.

I have a Results table with a TaskID and Name column, indicating who completed the task.

Relationship is active and 1:* (Assignments:Results)

The Results table has an "IsLatest" calculated column indicating either "Latest" or "Older" based on the most recent ("Latest") instance of a TaskID.

Here's what I need to do:

If the most recent instance of a TaskID in the Results table has a Name that does not match the ReviewerName indicated in the Assignments table for that TaskID, I want the ReviewerName updated in the Assignments table with the Name from the Results table.  Otherwise, I want the name to stay the same/be copied over.

The default value of this column should be the ReviewerName already indicated, and should only change if a more recent instance of the TaskID in the Results table has a name that does not match the Assignments table.

[technolog]

First, you want to update the ReviewerName in the Assignments table based on the most recent data from the Results table. The relationship between the tables is 1:* with Assignments being the one side and Results being the many side.

The Results table has a calculated column named IsLatest which indicates if that particular row is the most recent for a given TaskID.

To achieve your goal, you can create a measure or a calculated column in the Assignments table. I'll guide you through creating a calculated column since it seems more appropriate for this scenario.

Here's a DAX formula for a calculated column in the Assignments table:

NewReviewerName =
VAR CurrentTaskID = Assignments[TaskID]
VAR CurrentReviewerName = Assignments[ReviewerName]
VAR LatestReviewerFromResults =
CALCULATE(
MAX(Results[Name]),
FILTER(
Results,
Results[TaskID] = CurrentTaskID && Results[IsLatest] = "Latest"
)
)
RETURN
IF(
ISBLANK(LatestReviewerFromResults) || LatestReviewerFromResults = CurrentReviewerName,
CurrentReviewerName,
LatestReviewerFromResults
)
This formula does the following:

It first captures the current TaskID and ReviewerName from the Assignments table.
Then, it fetches the Name from the Results table where the TaskID matches and the IsLatest column is marked as "Latest".
Finally, it checks if the fetched name from the Results table is either blank or the same as the current ReviewerName. If it is, it retains the current ReviewerName. Otherwise, it updates it with the name from the Results table.
You can add this calculated column to the Assignments table, and it will give you the updated ReviewerName as per your requirements. If you want to replace the original ReviewerName, you can do so, but it's always a good idea to keep the original data intact and create a new column for such calculations.