Most recent record from another table

[Anonymous]

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

[fhill]

Create a New Column on Table 1 and here's your code...

NewestNote = LOOKUPVALUE(Table2[Notes], -- What you are trying to find related

    Table2[ID], Table1[ID], -- When ID matches ID

    Table2[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.)

[Anonymous]

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. 

[fhill]

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

[Anonymous]

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?

[fhill]

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?

[Anonymous]

It would be possible to have the same note/date/ID, but I'd say that would probably never happen.

[fhill]

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?  

[Anonymous]

This scenario would be so rare that it doesn't really matter. It could return either one or both. Whichever one is easier.

[fhill]

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

[Anonymous]

That only brought back a result for the first record:

ID	Column
123	Note 3
456
789

[fhill]

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

[Anonymous]

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])))

[fhill]

....?  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

[Anonymous]

I do. Thanks for trying to help! There must be something weird going on that someone else can hopefully answer.

[fhill]

I think I see what I did wrong... all of my Notes had 10/31 as dates...  DOH!  Let me poke at it more...

[fhill · Accepted solution]

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

[Anonymous]

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!