Forum Discussion

sabeensp's avatar
sabeensp
Helper IV
7 years ago
Solved

Most Recent Record

Hello Ihave a table "Table_1", which has multiple records of same employee on different date. I need to show only most recent record of the emploee.    Thanks for your help.      
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    create a calculated column using this DAX statement:

    Is most recent = 
var thisEmployee = 'Table_1'[Employee_Name]
var mostRecentDate = CALCULATE(MAX('Table_1'[Date]),ALL('Table1'),'Table_1'[Employee_Name] = thisEmployee)
return
IF('Table_1'[Date] = mostRecentDate,"yes","no")

    Now you can use this column to filter your table accordingly, use the column as a slicer, a visual level filter or even as report filter.

     

    Hopefully this is what you are looking for.

     

    Regards,

    Tom

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I've had the same issue and first solved it in DAX, using a suggestion like presented by TomMartens below. 

But I prefer solving it in Power Query, so I implemented the solution provided by kdlong, also below.

 

My table contains records that have an ID and an UPDATETIMESTAMP. The same ID can be updated multiple times.

I want a unique list of ID's with their latest UPDATETIMESTAMP.

 

I implemented both solutions in the same report and made a visual that shows the COUNT of ID and the UNIQUE COUNT of ID's.

And to my suprise these numbers were not equal... 

 

So, the problem was that some ID's were updated exactly at the same timestamp. Both implementations identified these both as the latest version.

 

The only correct solutiuon that worked for me was to sort the table (descending) on the UPDATETIMESTAMP and remove the duplicates on the ID column. Do NOT forget to sort the table using the "table.buffer" function. 

This is described in more detail here: https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/