Help Optimizing Consecutive Count Measure
Hello, I have been working on creating a way to count the consecutive periods (Months for this specific measure) where Occupancy % of our properties is below 95%. I started with code that was inspired by a video from SQLBI: Counting consecutive days with sales – Unplugged #47 - SQLBI This measure worked great, and was pretty fast but it does not work at the total level because it runs off the difference in Index between the reference point and the nearest hole. This index is a column value that corresponds to a single property, so when a table or matrix tries to total the value it just outputs a repeat of the final value in the table. So, I worked on a dynamic solution that works at all levels and I was able to do it, but it is very slow (16 seconds). Can anyone please look at this and help me find a way to optimize the code? I am attaching a table of reference data containing the pieces of the equation because my model is just too large to make a sample of. Table names will need adjusted since they all exist in one place, but the solution needs to assume original table names as the data is not actually stored/related this way. Sample Table Data File Consecutive Months Below Occupancy Threshold = VAR filtOcc = CALCULATETABLE( SUMMARIZE( 'Calendar', 'Calendar'[CurMonthOffset], "Occ%", CALCULATE( [Occupancy % No Management Date Filter], ALLSELECTED('General Community Information'[PropertyHMY]) ) ), ALL('Calendar') ) VAR filtNoOcc = CALCULATETABLE( VALUES('Calendar'[CurMonthOffset]), FILTER(filtOcc, [Occ%] < .95 && NOT([Occ%]) = BLANK()), All('Calendar') ) VAR Ref = MAX('Calendar'[CurMonthOffset]) VAR AllRef = ALL('Calendar'[CurMonthOffset]) VAR CountNoOcc = EXCEPT(AllRef, filtNoOcc) VAR NearestHole = MAXX( FILTER( CountNoOcc, [CurMonthOffset] <= Ref ), 'Calendar'[CurMonthOffset] ) VAR Result = Ref - NearestHole RETURN Result