Dax query taking too long and using to much memory.

[AlexS0902]

Hi,

I have created a measure that calculates concurrent charging sessions of ev chargers. The issue is when looking at a date range of larger than a few days, the measures takes way too long to load. On the published online version of the report, the visuals simply don't load at all and return an error. For this measure to be useful I really need to be able to easily filter the date range to at least a month.

The measure itself is:

ActiveChargingSessions =

VAR CurrentDateTime = MIN(TimeTable[TimeTable])

RETURN

CALCULATE(

    COUNTROWS('PowerBI - Transactions'),

    'PowerBI - Transactions'[start_charger_timestamp] <= CurrentDateTime &&

    'PowerBI - Transactions'[session_finish_timestamp] >= CurrentDateTime

)

With 'TimeTable' being a date/time table with every 10 minute interval (for the past year). And the start_charger_timestamp being the start time of the charge sessions and session_finish_timestamp being the finish time of the charging session.

Any suggestions to improve the performance of this measure, or ideas to calculate the same metric in a completely different way would be great cheers.

Thanks

Alex

[AlexS0902]

Hi @rajendraongole1 ,

Thank you so much. Your suggestion definitely helped. It still takes a little time to load but at least it does actually load and doesn't timeout.

It's still incredibly slow for when I'm using functions like MAXX and AVERAGEX with that same measure, so not sure if you have any further solutions to these? E.g.

Max active sessions =

MAXX(TimeTable, [ActiveChargingSessions])

[rajendraongole1 · Accepted solution]

Hi @AlexS0902 - if your dataset allows, consider pre-filtering the transactions table based on the date range before applying the date and time filters. This can significantly reduce the number of rows that need to be evaluated.

slight modifications on measure:

ActiveChargingSessions =
VAR CurrentDateTime = MIN(TimeTable[TimeTable])
VAR FilteredTransactions =
FILTER(
'PowerBI - Transactions',
'PowerBI - Transactions'[start_charger_timestamp] <= CurrentDateTime &&
'PowerBI - Transactions'[session_finish_timestamp] >= CurrentDateTime
)
RETURN
COUNTROWS(FilteredTransactions)

can you please check this and let know.