Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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/
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.
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
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
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
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |