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

fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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

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

    • fhill's avatar
      fhill
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

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

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