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

     

6 Replies

  • 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

  • Aree's avatar
    Aree
    Resolver I
    7 years ago

    An Alternate Solution Using M Query (Query Editor)

     

    The above is the core table that has a set of comments for four specific fields (Progress...Risk Related To_x002). 

    You will note that "title" field is like your Employee_Name field.

    Step 1:
    Generate a Group By table and a Join Key

    let
    Source = SharePoint.Tables("https://xxxxxx.sharepoint.com/", [ApiVersion = 15]),
    #"2954288a-f8dc-49fe-b6e4-fe0b8832150f" = Source{[Id="2954288a-f8dc-49fe-b6e4-fe0b8832150f"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"2954288a-f8dc-49fe-b6e4-fe0b8832150f",{{"ID", "ID.1"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "Created"}),
    #"Group By" = Table.Group(#"Removed Other Columns", "Title",{"Max CreatedDate", each List.Max([Created])}),
    #"Convert DateTime to Text" = Table.AddColumn(#"Group By", "DateTimeToText", each DateTime.ToText([Max CreatedDate])),
    #"Create Join Key" = Table.AddColumn( #"Convert DateTime to Text", "JoinKey", each Text.Combine({[Title],[DateTimeToText]},"-"))
in
    #"Create Join Key"

    Step 2:

    Replicate Join Key on Core Table

     

    Step 3:

    Join (merge (inner join)) using the join key and "presto" an alternate approach.

    let
    Source = SharePoint.Tables("https://xxxxx.sharepoint.com/", [ApiVersion = 15]),
    #"2954288a-f8dc-49fe-b6e4-fe0b8832150f" = Source{[Id="2954288a-f8dc-49fe-b6e4-fe0b8832150f"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"2954288a-f8dc-49fe-b6e4-fe0b8832150f",{{"ID", "ID.1"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "Progress", "Financial Progress", "Key Milestones", "Risk Related To_x002", "Created"}),
    #"Convert Created ToText" = Table.AddColumn(#"Removed Other Columns", "CreatedToText", each DateTime.ToText([Created])),
    #"Create Join Key" = Table.AddColumn(#"Convert Created ToText", "JoinKey", each Text.Combine({[Title],[CreatedToText]},"-")),
    #"Merged Queries" = Table.NestedJoin(#"Create Join Key", {"JoinKey"}, S_Commentary_GroupBy, {"JoinKey"}, "S_Commentary_GroupBy", JoinKind.Inner)
in
    #"Merged Queries"

     

    I like to use M Query as it allows me to hide all these transformation task but I do like the DAX solution provided its pretty kool and just as effective. 

    • kdlong's avatar
      kdlong
      Frequent Visitor
      4 years ago

      Aree, 

      This is a reasonable solution, but as your dataset grows, the inner merge can substantially degrade performance. As an alternative, I would suggest the following:
      - Group by 'Title' and find max date

      - Merge this grouping table into base table

      - Create a column testing if date is equal to max date

      - Filter for matches

      - Remove the uneeded columns

      Ex. :

       

      let
    Source = example_table,
    
    //Group by title
    group_title = Table.Group(Source, {"title"}, {{"date_max", each List.Max([date]), type nullable datetime}}),

    //Merge titlegroup into select columns step
    merge_group_title = Table.NestedJoin(Source, {"title"}, group_title, {"title"}, "group_title", JoinKind.LeftOuter),
    expand_group_title = Table.ExpandTableColumn(merge_group_title, "group_title", {"date_max"}, {"date_max"}),
    
    //Insert boolean test for most recent
    insert_col_newest = Table.AddColumn(expand_group_title, "bool_new", each [date] = [date_max], type logical),
    
    //Filter for newest rows
    select_rows_newest = Table.SelectRows(insert_col_newest, each ([bool_new] = true)),

    //Delete cols no longer needed
    delete_cols = Table.RemoveColumns(select_rows_newest,{"date_max", "bool_new"})
    
in
    delete_cols

       

       

  • 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/