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
Thanks! I see what this is trying to do, but I get an "A table of multiple values was supplied where a single value was expected" error. I've verified that my real Table 1 has unique IDs whereas Table 2 has multiple.
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 |
My data will be hard to cleanse to send, as it is a large dataset. I think I found the problem, although I don't know how to solve it. My date column in Table2 could have the same date/time for the same ID. If you change the date in the last line of your sample date for ID 789 to 10/8/2019 0:00 so that both 789 records have the same date you can recreate the error. Are you aware of any way around that?
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.