Forum Discussion
Splitting table rows from value
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 |
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])
5 Replies
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 Linkedinthank 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])
Anonymous -
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"
