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
Create a NEW Column on Table 1
That only brought back a result for the first record:
| ID | Column |
| 123 | Note 3 |
| 456 | |
| 789 |
I think I see what I did wrong... all of my Notes had 10/31 as dates... DOH! Let me poke at it more...
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 Are there more columns involved that might be causing issues? Did you create the new Column off Table 1? My Table 1 only has ID's, and Table 2 only has ID, Date, & Notes. Here's my code for the Custom Column off Table 1. Can you make this work as is below outside your data set? (Double check you are filtering Tables 2 where Table 1 ID = Table **2** ID.)
Column = CALCULATE( FIRSTNONBLANK( Table2[Notes], TRUE() ),FILTER( Table2, Table1[ID] = Table2[ID] && Table2[Date] = MAX(Table2[Date])))Table 1:
ID Column 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 456 10/31/2019 0:00 Note1a - Dup 789 10/8/2019 0:00 Note 1 789 10/31/2019 0:00 Note5 That is so odd. When it didn't work in my real dataset I thought it may be something else messing it up, so tried it with my sample date. As you can see, the formula is a copy and past of yours, but my results don't match up.
Column = CALCULATE( FIRSTNONBLANK( Table2[Notes], TRUE() ),FILTER( Table2, Table1[ID] = Table2[ID] && Table2[Date] = MAX(Table2[Date])))....? Assuming you have a 1 to many relationship between Table 1 & Table 2. Other than that, I hope someone else picks up this thread who might have some other ideas?
FOrrest
I do. Thanks for trying to help! There must be something weird going on that someone else can hopefully answer.
Woohoo! Got it to work in my sample and actual datasets! I actually already had a max date column so this worked out perfectly. Thanks so much for all of your help on this!