Forum Discussion
Solved
Slicing and Filtering records with duration
Hi All, I have a flat file event log that I am importing that has records for many assets over may days. I would like to visualise for any given time frame, whole or part of a day, how much dura...
Not my best work but this seems to give me the desired result...
FilteredDuration = SUMX ( Events, SWITCH ( TRUE (), Events[DateTime] > [MinDateTime] && Events[NextDateTime] < [MaxDateTime], Events[Durations (Seconds)], Events[DateTime] < [MinDateTime] && Events[NextDateTime] > [MinDateTime] && Events[NextDateTime] < [MaxDateTime], DATEDIFF ( [MinDateTime], Events[NextDateTime], SECOND ), Events[DateTime] > [MinDateTime] && Events[DateTime] < [MaxDateTime] && Events[NextDateTime] > [MaxDateTime], DATEDIFF ( Events[DateTime], [MaxDateTime], SECOND ), 0 ) )
MaxDateTime = MAXX((DateTable),[Date]) + MAXX((TimeTable),[Time])
MinDateTime = MINX((DateTable),[Date]) + MINX((TimeTable),[Time])
I have three cases for each depending on if the start or finish time is outside the filter to recalculate the duration.
Please let me know if there is a better way but hopefully, this might help someone at the very least.
Not my best work but this seems to give me the desired result...
FilteredDuration = SUMX ( Events, SWITCH ( TRUE (), Events[DateTime] > [MinDateTime] && Events[NextDateTime] < [MaxDateTime], Events[Durations (Seconds)], Events[DateTime] < [MinDateTime] && Events[NextDateTime] > [MinDateTime] && Events[NextDateTime] < [MaxDateTime], DATEDIFF ( [MinDateTime], Events[NextDateTime], SECOND ), Events[DateTime] > [MinDateTime] && Events[DateTime] < [MaxDateTime] && Events[NextDateTime] > [MaxDateTime], DATEDIFF ( Events[DateTime], [MaxDateTime], SECOND ), 0 ) )
MaxDateTime = MAXX((DateTable),[Date]) + MAXX((TimeTable),[Time])
MinDateTime = MINX((DateTable),[Date]) + MINX((TimeTable),[Time])
I have three cases for each depending on if the start or finish time is outside the filter to recalculate the duration.
Please let me know if there is a better way but hopefully, this might help someone at the very least.