Forum Discussion

jvandyck's avatar
jvandyck
Icon for Helper IV rankHelper IV
8 years ago

time elapsed across multiple records

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:

 

 EmployeeDate IPStartEndDuration
A7/06/201810.101.11708:00:00.000000016:00:00.0000000480
A7/06/201810.101.117.5413:00:00.000000017:00:00.0000000240
A7/06/201810.101.201.17819:00:00.000000020:00:00.000000060
A7/06/201810.101.201.13619:30:00.000000020:30:00.000000060

 

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.

Need Help
Tips and Tricks

3 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      jvandyck
      Icon for Helper IV rankHelper IV
      8 years ago

      Creative! But with 1500 employees and multiple lines per user, this will not work i think

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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