Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 int...
Need Help
  • fhill's avatar
    fhill
    6 years ago

     

    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:

    IDMax DateColumn 2
    12310/31/2019 0:00Note 3
    45611/2/2019 0:00Note1a - Dup
    55911/1/2019 0:00NEW NOTE
    78910/31/2019 0:00Note5

     

    Table 2:

    IDDateNotes
    1238/20/2019 0:00Note 1
    12310/30/2019 0:00Note 2
    12310/31/2019 0:00Note 3
    4565/1/2019 0:00Note 1
    4565/6/2019 0:00Note 2
    4565/30/2019 0:00Note 3
    4566/1/2019 0:00Note 4
    45610/31/2019 0:00Note1a
    45611/2/2019 0:00Note1a - Dup
    55911/1/2019 0:00NEW NOTE
    78910/8/2019 0:00Note 1
    78910/31/2019 0:00Note5
fhill's avatar
fhill
Resident Rockstar
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      fhill
      Resident Rockstar
      6 years ago

      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

      IDNewestNote
      123Note 3
      456Note1a
      789Note5

       

       

      Table 2

      IDDateNotes
      1238/20/2019 0:00Note 1
      12310/30/2019 0:00Note 2
      12310/31/2019 0:00Note 3
      4565/1/2019 0:00Note 1
      4565/6/2019 0:00Note 2
      4565/30/2019 0:00Note 3
      4566/1/2019 0:00Note 4
      45610/31/2019 0:00Note1a
      78910/8/2019 0:00Note 1
      78910/31/2019 0:00Note5
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?