Help Optimizing Consecutive Count Measure

The measure you've provided is trying to count the consecutive months where the Occupancy % is below 95%. The main issue you're facing is that the measure is slow, especially at the total level.

The measure you've written is quite complex, and the multiple table scans (CALCULATETABLE, FILTER, etc.) are likely the cause of the performance issue. Let's try to simplify and optimize it.

First, let's understand the logic:

filtOcc is creating a table that summarizes the Occupancy % for each month.
filtNoOcc is filtering out months where Occupancy % is above 95% or is blank.
Ref is getting the latest month in the current context.
AllRef is getting all months.
CountNoOcc is getting all months that are not in filtNoOcc.
NearestHole is finding the nearest month (from the current context) that has an occupancy above 95% or is blank.
Finally, Result is calculating the difference between the current month and the NearestHole.
Now, let's try to simplify:

Instead of creating multiple tables, we can try to work with a single table and iterate over it. We can use the EARLIER function to compare values within the same table.

Here's a more streamlined version:

Consecutive Months Below Occupancy Threshold =
VAR CurrentMonth = MAX('Calendar'[CurMonthOffset])
VAR ConsecutiveCount =
COUNTROWS(
FILTER(
ALL('Calendar'),
'Calendar'[CurMonthOffset] <= CurrentMonth &&
CALCULATE(
[Occupancy % No Management Date Filter],
ALLSELECTED('General Community Information'[PropertyHMY])
) < 0.95
)
) -
COUNTROWS(
FILTER(
ALL('Calendar'),
'Calendar'[CurMonthOffset] <= CurrentMonth &&
CALCULATE(
[Occupancy % No Management Date Filter],
ALLSELECTED('General Community Information'[PropertyHMY])
) >= 0.95
)
)
RETURN
ConsecutiveCount
This version first counts all the months below 95% occupancy up to the current month and then subtracts the count of months above 95% occupancy up to the current month. The difference gives us the consecutive months below 95%.

This should be faster because it reduces the number of table scans and calculations. However, the real performance will depend on the size and complexity of your data model. Always test any new measure in your environment to ensure it meets your performance and accuracy requirements.