Forum Discussion
Helper IVtime elapsed across multiple records
Community Championthat's an interesting one, I have solution, but I fear performance may be terrible with bigger dataset
the concept is following
1) I created a table of time ticks (every one minute in my case, you may need to adjust it to get desired precision/performace)
Time
00:00:00
00:01:00
00:02:00
00:03:00
00:04:00
...
up to 23:59:00
2) generate a cartesian product of that table with the one that you provided
3) check if the time ticks are between Start/End,
4) filter only those ticks
5) group by to get unique logged in ticks
6) count the ticks to and divide by 60 to get the result in hours
Final measure (covers steps 2-6)
LoggedInTime :=
VAR TimePerUser =
GENERATE ( 'Time', 'Table' )
VAR TotalTimeSummary =
ADDCOLUMNS (
TimePerUser,
"TimeFlag", 'Time'[Time] >= [Start]
&& 'Time'[Time] < [End]
)
VAR OnlyLoggedTime =
FILTER ( TotalTimeSummary, [TimeFlag] = TRUE )
VAR UniqueTimeTicks =
GROUPBY ( OnlyLoggedTime, 'Time'[Time] )
RETURN
COUNTROWS ( UniqueTimeTicks ) / 60
Helper IVCreative! But with 1500 employees and multiple lines per user, this will not work i think
- Stachu
well, it already works for multiple users and multiple lines per user, the question is performance
for the example you posted it gives 10,5, adding other users works as well
EDIT - unless your point is about aggregating times of different users - then you need to add user in GroupBy