Forum Discussion
Most recent record from another table
Here you go, FINALLY! 2 Custom Columns required...
Max Date = CALCULATE(MAX(Table2[Date])) ** Make this one First, to make Part 2 below easier. **
Column 2 = CALCULATE(LASTNONBLANK(Table2[Notes],TRUE()), FILTER( Table2, Table1[ID] = Table2[ID] && Table2[Date] = Table1[Max Date]))** You don't have to display Max Date, it just has to be another Custom Column on the table. **
Table 1:
ID Max Date Column 2 123 10/31/2019 0:00 Note 3 456 11/2/2019 0:00 Note1a - Dup 559 11/1/2019 0:00 NEW NOTE 789 10/31/2019 0:00 Note5 Table 2:
ID Date Notes 123 8/20/2019 0:00 Note 1 123 10/30/2019 0:00 Note 2 123 10/31/2019 0:00 Note 3 456 5/1/2019 0:00 Note 1 456 5/6/2019 0:00 Note 2 456 5/30/2019 0:00 Note 3 456 6/1/2019 0:00 Note 4 456 10/31/2019 0:00 Note1a 456 11/2/2019 0:00 Note1a - Dup 559 11/1/2019 0:00 NEW NOTE 789 10/8/2019 0:00 Note 1 789 10/31/2019 0:00 Note5
Yep, that did cause me to duplicate your error. When you have ID & Date Duplicates, are the Notes the same as well? (Then you can freely select any value since they are all the same?)
Or, are the notes different for the ID/Date Duplicates? In this case, what logic should be used to select the First / Last Note, or do you want an error message saying 'Duplicate' or something else to populate?
It would be possible to have the same note/date/ID, but I'd say that would probably never happen.
So, for Same ID/Dates, but different Notes, do you want to see BOTH Notes, or what logic should be used to select one of the Notes?
This scenario would be so rare that it doesn't really matter. It could return either one or both. Whichever one is easier.
Create a NEW Column on Table 1
Column = CALCULATE( FIRSTNONBLANK( Table2[Notes], TRUE() ), -- Get FIRST Non Blank Value When...FILTER( Table2, Table1[ID] = Table2[ID] && Table2[Date] = MAX(Table2[Date]))) -- Table 1 ID = Table 2 ID and Table 2 Date = MAX Table 2 Date