Forum Discussion
Most recent record from another table
I have two tables joined by ID. Table 1 is just a list of unique IDs and Table 2 has multiple IDs with a note and date for each. I need to add a column to Table 1 to bring in the most recent note into the first table. Example:
Table 1:
| ID |
| 123 |
| 456 |
| 789 |
Table 2:
| ID | Date | Notes |
| 123 | 10/31/2019 | Note 3 |
| 123 | 10/30/2019 | Note 2 |
| 123 | 8/20/2019 | Note 1 |
| 456 | 6/1/2019 | Note 4 |
| 456 | 5/30/2019 | Note 3 |
| 456 | 5/6/2019 | Note 2 |
| 456 | 5/1/2019 | Note 1 |
| 789 | 10/8/2019 | Note 1 |
Desired result in Table 1:
| ID | Note |
| 123 | Note 3 |
| 456 | Note 4 |
| 789 | Note1 |
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
17 Replies
Create a New Column on Table 1 and here's your code...
NewestNote = LOOKUPVALUE(Table2[Notes], -- What you are trying to find relatedTable2[ID], Table1[ID], -- When ID matches IDTable2[Date], CALCULATE(MAX(Table2[Date]))) -- When Date is equal to MAX Date(CALCULATE is needed to look 'by line' of Table 1 - If you just use MAX without it, you'll only get 1 value for the max of the whole table.)I went back and added duplicate dates on my Table 2 per ID, but still didn't get the error you mentioned. Do you have any way to cleanse and post your data tables?
FOrrest
Table1
ID NewestNote 123 Note 3 456 Note1a 789 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 789 10/8/2019 0:00 Note 1 789 10/31/2019 0:00 Note5