Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabeensp
Helper IV
Helper IV

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.

 

 

2019-04-22_13-26-44.png

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
MarcoLuiten
New Member

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/

 

 

Aree
Resolver I
Resolver I

An Alternate Solution Using M Query (Query Editor)

 

Core table.png

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

Group By Table.png

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
JoinKey Replication.png

 

Step 3:

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

Result.png

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
Frequent Visitor

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

 

 

TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Hey @Anonymous ,

 

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.