Show only last user that accessed the object last (Max)

[PBI_Monkey]

Hi all,

I have a list like this which shows when a User Last acccessed an Object:

User_Name	Start_Time	Object_Name
User1	1/01/2023 22:07	Report1
User2	1/10/2023 22:07	Report1
User3	5/09/2023 22:07	Report2
User1	5/09/2023 22:07	Report3
User3	7/10/2023 22:07	Report3
User2	23/10/2023 22:07	Report3

I want to show when the Object was accessed last and by which User:

Want to show only last user that accessed the object last
User_Name	Start_Time	Object_Name
User2	1/10/2023 22:07	Report1
User3	5/09/2023 22:07	Report2
User2	23/10/2023 22:07	Report3

Any ideas on how I can achieve this without using SQL? 

I have tried achieving this with 'Latest' in Power Bi but that doesn't give 1 last user by Object that it was accessed.

Thanks

[Ritaf1983 · Accepted solution]

Hi @PBI_Monkey 
You can create 2 measures :
1 for the latest start time by object :

last time of object =

var

grouped_table =

    SUMMARIZE('Table','Table'[Object_Name],"max start time",max('Table'[Start_Time]))

return

maxx(grouped_table,max([max start time]))

2. for last user based on the first measure :

last_user =

VAR vTable = VALUES('Table'[User_Name] )

VAR last_time_object = [last time of object]

VAR Keepmin = FILTER( vTable, max('Table'[Start_Time]) = [last time of object] )

VAR last_user = IF( [last time of object] >0, CONCATENATEX( Keepmin, [User_Name], ", "))

RETURN

last_user

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User_Name", type text}, {"Start_Time", type datetime}, {"Object_Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Object_Name"}, {{"All", each Table.Max(_,"Start_Time")}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"User_Name", "Start_Time"}, {"User_Name", "Start_Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{{"Object_Name", type text}, {"User_Name", type text}, {"Start_Time", type datetime}})
in
    #"Changed Type1"

Hope this helps.

[Ritaf1983 · Accepted solution]

Hi @PBI_Monkey 
You can create 2 measures :
1 for the latest start time by object :

last time of object =

var

grouped_table =

    SUMMARIZE('Table','Table'[Object_Name],"max start time",max('Table'[Start_Time]))

return

maxx(grouped_table,max([max start time]))

2. for last user based on the first measure :

last_user =

VAR vTable = VALUES('Table'[User_Name] )

VAR last_time_object = [last time of object]

VAR Keepmin = FILTER( vTable, max('Table'[Start_Time]) = [last time of object] )

VAR last_user = IF( [last time of object] >0, CONCATENATEX( Keepmin, [User_Name], ", "))

RETURN

last_user

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[PBI_Monkey]

Thanks for the quick reply @littlemojopuppy, but how can I achieve this.

Apologies, I'm new to Power Bi 😉

[littlemojopuppy]

@PBI_Monkey What have you tried in the five minutes since I replied? 😉

[littlemojopuppy]

LASTNONBLANK() should work.  No such thing as LATEST() 😉