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
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.
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 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 DateThat only brought back a result for the first record:
ID Column 123 Note 3 456 789 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!