Help needed resolving DAX performance on parking events data coming from sensor system

[gayatrikrishnan]

 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 suburbs as well.
User is presented with suburb, street, date and time filters. Based on the user selected time the occupancy minutes has to be calculated for that selected time only.

Power BI shared Dataset is used and data is brought from HANA and total records in the dataset is 4.5 million rows and it can grow further.
For each suburb for a month there are min of 45K rows to maximum 350K rows

The measures works when a month and a suburb is selected - Total rows that it works with is 50K rows.
When tried to run for a month where total events are more than 50K rows it doesn't work and Power BI throws memory error

There are couple of issues that I am not sure of
1 - Events fact has relationship to Parking Rules dimension on 3 columns - RuleID, Day of the event and time of the event
I have joined on two columns in power query but as time part is a non equi join it is not part of the join which creates many to many relationship. Rule ID's are not unique and is being used from source system directly.
2 - Date slicer added is working on a custom logic as I need to select events that occurred between dates. Event Start and end dates are used to figure out if event is part of the date filter or not. Following is the DAX I am using
SelectedStartDateTime and SelectedEndDateTime is a measure that is part of date dimension table and it calculated by using max and min date

DEFINE
MEASURE 'EVENTS'[UserDateTimeFilter] =

Var StartDate = MAX(EVENTS[EVENT_START_TIME_TS])
Var EndDate = MAX(EVENTS[EVENT_END_TIME_TS])
Var StartProgress = IF(and(StartDate>=[SelectedStartDateTime],StartDate<=[SelectedEndDateTime]),1,0)
Var BetweenProgress = IF (AND([SelectedStartDateTime]>= StartDate, [SelectedEndDateTime]<=EndDate),1,0)
Var EndProgress = IF (and(EndDate>=[SelectedStartDateTime], EndDate<=[SelectedEndDateTime]),1,0)
RETURN
IF(StartProgress = 1 || BetweenProgress = 1 || EndProgress = 1,1,0)

3 - For each event I am calculating the no.of minutes the event occurred within the associated rule time. Around 5% events can span multiple dates and be associated with different rules for different days.
Following is the DAX that I have written to acheieve the results
DEFINE MEASURE 'EVENTS'[OccupancySecondsInRule] =

Var EventStartTime = IF(max(EVENTS[EVENT_START_TIME_TS]) < [SelectedStartDatetime],[SelectedStartDatetime],max(EVENTS[EVENT_START_TIME_TS]))
Var EventEndTime = IF(max(EVENTS[EVENT_END_TIME_TS]) > [SelectedEndDatetime],[SelectedEndDatetime],max(EVENTS[EVENT_END_TIME_TS]))

Var RuleStartTime = IF(ISBLANK(max(EVENTS[Parking Rule.TimeFrom])),DATEVALUE("1/1/2000 00:00:00"),CONVERT(FORMAT(EventStartTime,"mm/dd/yyyy") & " " & FORMAT(max(EVENTS[Parking Rule.TimeFrom]),"hh:nn:ss"),DATETIME))
Var RuleEndTime = IF(ISBLANK(max(EVENTS[Parking Rule.TimeTo])),DATEVALUE("1/1/2000 00:00:00"),CONVERT(FORMAT(EventEndTime,"mm/dd/yyyy") & " " & FORMAT(max(EVENTS[Parking Rule.TimeTo]),"hh:nn:ss"),DATETIME))

RETURN
SWITCH(
TRUE(),
EventStartTime > RuleEndTime || EventEndTime < RuleStartTime,0,
EventStartTime >= RuleStartTime && EventEndTime <= RuleEndTime, DATEDIFF(EventStartTime,EventEndTime,SECOND),
EventStartTime < RuleStartTime && EventEndTime <= RuleEndTime, DATEDIFF(RuleStartTime,EventEndTime,SECOND),
EventStartTime >= RuleStartTime && EventEndTime > RuleEndTime, DATEDIFF(EventStartTime,RuleEndTime,SECOND)
)

Finally the percentages are calculated using SUMX to calculate total time for each event within rule and user's selected time
DEFINE MEASURE 'EVENTS'[OccupancyTotalTimeInRule] =
SUMX(FILTER(EVENTS,[UserDateTimeFilter] = 1 && [OccupancySecondsInRule] > 0),[OccupancySecondsInRule])/60

DEFINE MEASURE 'EVENTS'[OccupancyPercentage] =
Var DistinctBays = CALCULATE
(
DISTINCTCOUNT(EVENTS[BAY_ID]),
FILTER(EVENTS,[UserDateTimeFilter] = 1)
)
RETURN [OccupancyTotalTimeInRule]/(([PossibleOccupancyMinutes] * COUNT(CV_SAP_TIME_DIM[DATE_SQL])) * DistinctBays)

I have tried displaying detailed events for a suburb for a month with measure OccupancySecondsInRule and it seems to return fine after few seconds.
When the data is run at aggregate level using OccupancyPercentage measure which internally uses OccupancyTotalTimeInRule. It is not performing as expected for more than 50K rows.
I have a feeling it is probably due to SUMX function but I am not sure and have no idea what is the replacement for that as I need the calculation to occur at event level.

Sample data and pbix files are in the following folder

https://drive.google.com/drive/folders/1Zx3kVA3skU9XDcZKfRDS31UYYc6uW2d8?usp=sharing

Any thoughts on how to address out of memory error and slow performance.

[gayatrikrishnan]

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.

Thanks,

Gayatri

[mahoneypat]

Can you provide a pbix with example data so a specific solution can be suggested?  You may be able to get there with a DAX only change, but your solution is probably to make some data model changes.  DateTime columns mean high granularity and the potential for slow performance.  You might want to consider splitting your DateTime columns into separate Date and Time columns, and then adding a Date table and a Time table to your model, so you can iterate independently over both.  Another approach to consider is to "round" your DateTimes to the nearest 15 min, 30 min, etc. depending on the timeframe your care about in your analysis.

Pat

[gayatrikrishnan]

Thanks for your reply.

I have added sample data and the test report that mimics the relationships and measures.

Thanks,

Gayatri