Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
Hi @PBI_Monkey
You can create 2 measures :
1 for the latest start time by object :
2. for last user based on the first measure :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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.
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.
Hi @PBI_Monkey
You can create 2 measures :
1 for the latest start time by object :
2. for last user based on the first measure :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks for the quick reply @littlemojopuppy, but how can I achieve this.
Apologies, I'm new to Power Bi 😉
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
76 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |