Forum Discussion
Most Recent Record
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
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