Events in progress - working but has become very slow

[dnewton]

Hi All,

I have a calculated column for a software application which counts how many sessions are "concurrent" with another session (in other words 'events in progress'). As below:

Concurrent with this session = 
COUNTROWS (
    FILTER (
        Sessions,
        Sessions[Id] <> EARLIER(Sessions[Id])
            && NOT (Sessions[End] < EARLIER(Sessions[Start] ) || Sessions[Start] > EARLIER(Sessions[End]) )
    )
) +1

This has worked fine for several months but recently as the size of my database has grown to around 5,000 rows it is now taking a very long time to update and is timing out in the Power Bi Service causing the dataset to not refresh.

Is there a way I can "optimise" this to improve performance? 

I found another suggestion elsewhere (as below) but have had no luck translating this for my data:

=Calculate(
CountRows(FactInternetSales),
Filter(Values(FactInternetSales[OrderDateKey]), Min(DimDate[DateKey])>=[OrderDateKey]),
Filter(Values(FactInternetSales[ShipDateKey]), Max(DimDate[DateKey])<=[ShipDateKey]))

[v-lili6-msft · Accepted solution]

hi  @dnewton 

If you may try this formula as below:

Concurrent with this session = 
var _Id=Sessions[Id]
var _Start=Sessions[Start]
var _End=Sessions[End]
Return
COUNTROWS (
    FILTER (
        Sessions,
        Sessions[Id] <> _Id
            && NOT (Sessions[End] < _Start || Sessions[Start] > _End )
    )
) +1

or

Concurrent with this session = 
var _Id=Sessions[Id]
var _Start=Sessions[Start]
var _End=Sessions[End]
Return
COUNTROWS (
    FILTER (
        Sessions,
        Sessions[Id] <> _Id
            &&  (Sessions[End] >= _Start && Sessions[Start] <= _End )
    )
) +1

Regards,

Lin

[dnewton]

This worked flawlessly - thank you!

[Greg_Deckler]

Maybe something in this series of articles will help: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275