time elapsed across multiple records

[jvandyck]

Hi

Looking for help on this challenge. I have a tabel with logon/off events per employee and per ip adress. Based on this information I want to get the total active time per day per user, across ip addresses.

An example table looks like this:

Employee	Date	IP	Start	End	Duration
A	7/06/2018	10.101.117	08:00:00.0000000	16:00:00.0000000	480
A	7/06/2018	10.101.117.54	13:00:00.0000000	17:00:00.0000000	240
A	7/06/2018	10.101.201.178	19:00:00.0000000	20:00:00.0000000	60
A	7/06/2018	10.101.201.136	19:30:00.0000000	20:30:00.0000000	60

As you see the duration needs to be calculated across the different records and in between records there can be wholes as well as overlap. The total time to be calculated in this example would be 10.5 hours, and not just the sum of duration.

[Stachu]

that'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

[jvandyck]

Creative! 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