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

     

TomMartens's avatar
TomMartens
Super User
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
4 years ago

How would you tweak this formula to base on 'City' as well?

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    Hey Anonymous ,

     

    store the current city to a variable, and use this variable to extend the filter that calculates the max date.

     

    Regards,

    Tom