Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need to calculate the duration between Events for each particular user working in a specific file in a datasource that contains info from multiple users. Sample table below.
The goal is to get the Duration column to calculate the duration for the next user action in the same FileName.
A manual calculation in excel would be something like the following image.
So, basically, I want to subtract the next datetime row for the same user and filename to the current row.
If such next row does not exist then set the duration to 0 (as for the FileClose at the end, for instance).
Thank you for your help.
DateTime | Username | FileName | EventName | ViewName | Duration |
18/02/2020 14:00:00 | Ann | A | FileOpen | - | 00:00:00 |
18/02/2020 14:10:00 | Ann | A | ViewActivated | Level 2 - WRK | 01:00:00 |
18/02/2020 14:20:00 | Ann | B | FileOpen | - | 00:00:00 |
18/02/2020 14:30:00 | Ann | B | ViewActivated | Level 3 | 00:50:00 |
18/02/2020 14:40:00 | Ben | A | FileOpen | - | 00:00:00 |
18/02/2020 14:50:00 | Ben | A | ViewActivated | Level 2 - WRK | 00:10:00 |
18/02/2020 15:00:00 | Ben | A | ViewActivated | Legend | 00:30:00 |
18/02/2020 15:10:00 | Ann | A | Idle | - | 00:30:00 |
18/02/2020 15:20:00 | Ann | B | FileClose | - | 00:00:00 |
18/02/2020 15:30:00 | Ben | A | FileClose | - | 00:00:00 |
18/02/2020 15:40:00 | Ann | A | FileClose | 00:00:00 |
Solved! Go to Solution.
Try
DateDiff = 'view-activated collected'[DateTime] - MINX(FILTER('view-activated collected',
'view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath])
&& 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username])
&& 'view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime])
),'view-activated collected'[DateTime])
You can do in Power Query as well:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSN7TQNzIwMlAwNLEyMAAiJR0lx7w8EAnEbpk5qf4FqSCuLhAbQJXE6qBrNcTQGpaZWu6YXJJZlliSmgLk+6SWpeYoGCnoKoQHeQP5hjjNMkIxy4kUZxhjaMXuDGOwKaY4TDGBmuKUSnI4mGJoJRQOBtCwQzfLFB4d+MxKT81LARtijMMQzIjxTMlJhfsDlzbsceCck1+cSiAMTOGRgBp8xOk1wZoEYXqRtcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t, Username = _t, FileName = _t, EventName = _t, ViewName = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}, {"Username", type text}, {"FileName", type text}, {"EventName", type text}, {"ViewName", type text}, {"Duration", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Username", "FileName"}, {{"Grouped", each _, type table [DateTime=text, Username=text, FileName=text, EventName=text, ViewName=text, Duration=time]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Grouped],"Index",1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"NextDateTime", each tblName{[Index]}[DateTime])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}, {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom", {{"NextDateTime", null}}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Grouped", "AddIndex"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateTime", type datetime}, {"NextDateTime", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "DurationCalculated", each Duration.TotalMinutes([NextDateTime]-[DateTime]))
in
#"Added Custom2"
Proud to be a Super User!
Try like. Add or remove conditions as per need
Datediff = table[datetime] = minx(filter(table,table[file name] = earlier(table[file name]) && table[user name] = earlier(table[user name])
&& table[datetime] > earlier(table[datetime])),table[datetime])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
thank you for such quick reply ,
I am getting an error of type
"Too few arguments were passed to the MINX function. The minimum argument count for the function is 2."
the actual formula I am using is this
DateDiff = 'view-activated collected'[DateTime] = MINX(FILTER('view-activated collected','view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath] && 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username]) && ('view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime])),'view-activated collected'[DateTime])))
Try
DateDiff = 'view-activated collected'[DateTime] - MINX(FILTER('view-activated collected',
'view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath])
&& 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username])
&& 'view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime])
),'view-activated collected'[DateTime])
It is working very well, thank you.
Found an unintended consequence of the requirement, though.
Can the last DateDiff calculation for the last entry for user+docpath be set to 0?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |