Forum Discussion
Help needed resolving DAX performance on parking events data coming from sensor system
Hi I am developing a report that calculates occupancy percentage for parking events. The report is supposed to be run for a suburb for a quarter or month. User can run this report for all the subur...
I have narrowed down the problem and found it is SUMX that is causing the issue. I made changes to Occupancy percentage formula by passing filtered table to sumx. Following is the rewritten measure.
OccupancyPercentage =
Var FilteredTable = FILTER(CC_OCCUPANCY_POC,[UserDateTimeFilter] = 1)
Var DistinctBays = CALCULATE
(
DISTINCTCOUNT(CC_OCCUPANCY_POC[BAY_ID]),
FilteredTable
)
Var OccupancyTotalTimeInRule = SUMX(CALCULATETABLE(
CC_OCCUPANCY_POC,
FilteredTable
),[OccupancySecondsInRule])/60
RETURN OccupancyTotalTimeInRule/(([PossibleOccupancyMinutes] * COUNT('CV_SAP_TIME_DIM'[DATE_SQL])) * DistinctBays)
This measure works with even with 350K rows and returns results in 2 mins. I need this to be improved and make it a bit faster. Any ideas.
This measure works with even with 350K rows and returns results in 2 mins. I need this to be improved and make it a bit faster. Any ideas.
Thanks,
Gayatri