cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Concurrency/events-in-progress: need to count how many concurrent events by same user

I have a data model with a single table 'Sessions' (1.5M rows) with columns for UserId, StartTime, and EndTime. A user can have multiple sessions throughout the day, and sometimes they are concurrent meaning a 2nd session will start after the first one starts but before it ends, and the 2nd session can end before or after the first event. Each event is typically under 30 seconds.

 

I am trying to create a calculated column that has the count of other sessions by the same UserId anytime during the duration of the current session. This is what I came up with and it takes >45 minutes to run and I am not sure if the results are even correct, but in general is there anything I can do to improve performance? 

 

isConcurrent = 
VAR currentUID = 'Sessions'[UID]
VAR currentStart = 'Sessions'[StartTime]
VAR currentEnd = 'Sessions'[EndTime]

VAR t=CALCULATETABLE('Sessions', FILTER('Sessions', //Should there be an All(Sessions) here?
'Sessions'[UID] = currentUID &&
(
    ('Sessions'[StartTime] <= currentStart &&
    ('Sessions'[EndTime] > currentStart && 'Sessions'[EndTime] <= currentEnd)) // O2
|| 
    ('Sessions'[StartTime] <= currentStart &&
    'Sessions'[EndTime] >= currentEnd) //O1
||
    (('Sessions'[StartTime] >= currentStart && 'Sessions'[StartTime] <= currentEnd) &&
    'Sessions'[EndTime] >= currentEnd) //O3
||
    (('Sessions'[StartTime] >= currentStart && 'Sessions'[StartTime] <= currentEnd) &&
    'Sessions'[EndTime] <= currentEnd) //O4
   )
 )
)
RETURN
COUNTROWS(t)

  

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Concurrency/events-in-progress: need to count how many concurrent events by same user

Hi @hymieho3 

How about this measure?

Measure =
CALCULATE (
    COUNT ( Sessions[UID] ),
    FILTER (
        ALLEXCEPT ( Sessions, Sessions[UID] ),
        NOT (
            [EndTime] < MAX ( Sessions[StartTime] )
                || [StartTime] > MAX ( Sessions[EndTime] )
        )
    )
)

Capture10.JPGCapture9.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Highlighted
Microsoft
Microsoft

Re: Concurrency/events-in-progress: need to count how many concurrent events by same user

The measure is not very performant--it actually makes my machine with 24GB RAM run out of resources when added to a table with the full dataset. Also I am not sure that the measure captures all of the overlap scenarios:

 

gray.PNG

 

I am more looking for data modeling strategies... For example if I were just looking at the day grain then I could use a date table to filter. But the StartTime and EndTime are down to milleseconds so I am at a loss. Or maybe I can use a date table to at least narrow the scope of the events to compare (events by the same user on the same day), and then do comparison against the events within scope. I tried this approach using a table variable, but I cannot access the columns of a table variable for the 2nd part so I am stuck. 

Highlighted
Microsoft
Microsoft

Re: Concurrency/events-in-progress: need to count how many concurrent events by same user

The measure is not very performant--it actually makes my machine with 24GB RAM run out of resources when added to a table with the full dataset. Also I am not sure that the measure captures all of the overlap scenarios:

 

gray.PNG

 

I am more looking for data modeling strategies... For example if I were just looking at the day grain then I could use a date table to filter. But the StartTime and EndTime are down to milleseconds so I am at a loss. Or maybe I can use a date table to at least narrow the scope of the events to compare (events by the same user on the same day), and then do comparison against the events within scope. I tried this approach using a table variable, but I cannot access the columns of a table variable for the 2nd part so I am stuck. 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors