09-20-2021 09:38 AM - last edited 09-21-2021 06:01 AM
I recently had the pleasure of collaborating with @dkernen in this thread on a variation of Cthulhu, a repeating consecutive counter indexing DAX solution. Now, when I created the original Cthulhu measure, I joked that if you found yourself needing that measure that you were likely only the second person to do so. Oh how wrong I was! The need for a solution like Cthulhu has been an infrequent yet consistent request in the forums. Cthulhu remains one of the top 30 most kudo'd posts in the Quick Measures Gallery and has been viewed over 20,000 times. For reference, most Quick Measure Gallery submissions get viewed a few thousand times. My most popular measure, Measure Totals, The Final Word, an issue that applies to anyone who has ever used DAX, has been viewed about 90,000 times. So, for a measure that "nobody will ever need" to be viewed roughly a quarter of times compared to a measure that literally everyone needs at some point; well, that's saying something about its popularity! In fact, by the time I wrote my book, DAX Cookbook, Cthulhu was popular enough that I included a version of it in Chapter 12: Applying Advanced DAX Patterns under the far less fanciful name, Repeating counter with criteria.
The basic problem of Cthulhu is to have a counter that increments on consecutive rows given that a specified criteria remain the same. In the event of a change, the counter should reset. The original Cthulhu measure relied on an Index column being present in the data in order to determine "previous" rows. The problem @dkernen came to me with required this indexing to be determined dynamically based upon user selections in the report. Thus, after a fair amount of remembering why Cthulhu is named Cthulhu, I was able to adapt the solution to the requirements and thus Bride of Cthulhu was born! @dkernen also arrived at an equivalent solution based upon her prior work and we dubbed that one "Bridesmaid of Cthulhu". Upon me asking, @dkernen was gracious enough to allow me to scrub the data and present the solutions here so that others might benefit.
Without further ado, here is Bride of Cthulhu and Bridesmaid of Cthulhu!
Bride of Cthulhu = VAR __referralDT = MIN(factCase[Referral_DT]) // The current date/time of the case VAR __group = MAX('dimODisp'[ORecovered_N]) // The current "group", what we are detecting changes of VAR __tmpTable1 = // Get the table to analyze (dimODisp) and add the date/time of each case FILTER( ADDCOLUMNS( ALLSELECTED('dimODisp'), "__Referral_DT", VAR __referralID = [Referral_ID] RETURN MINX(FILTER(ALL('factCase'),'factCase'[Referral_ID]=__referralID),'factCase'[Referral_DT]) ), [__Referral_DT]<=__referralDT ) VAR __tmpTable1a = // Add a dynamic index column ADDCOLUMNS( __tmpTable1, "__Index",COUNTROWS(FILTER(__tmpTable1,[__Referral_DT]<=EARLIER([__Referral_DT]))) ) VAR __tmpTable1b = FILTER(__tmpTable1a,[ORecovered_N] = __group) // Filter for only the same group as current VAR __tmpTable2 = // Add difference column of index values ADDCOLUMNS( __tmpTable1b, "__diff",[__Index] - MAXX(FILTER(__tmpTable1b,[__Index]<EARLIER([__Index])),[__Index])) VAR __max = MAXX(__tmpTable2,[__Index]) // Get max index in table VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[__Index]) // Get max index after a change VAR __tmpTable3 = FILTER(__tmpTable2,[__Index]>=__maxStart) // Get all rows after last change RETURN SWITCH(TRUE(), __group = 0,0, // If failure, return 0 ISBLANK(__max),1, // If no previous rows, return 1 __max=__maxStart,1, // If first in group, return 1 COUNTROWS(__tmpTable3) // Return # of consequetive rows in group )
Bridesmaid of Cthulhu = VAR CurDT = SELECTEDVALUE ( factCase[Referral_DT] ) VAR ResetGroup = [Consec Reset_DT] VAR FirstFailure = MINX ( CALCULATETABLE ( ALLSELECTED ( factCase ), dimODisp[ORecovered] = "No" ), factCase[Referral_DT] ) VAR BeforeFail = IF ( ResetGroup < FirstFailure, 1, 0 ) VAR counter = COUNTROWS ( CALCULATETABLE ( factCase, FILTER ( ALLSELECTED ( factCase ), [Consec Referral_DT] <= CurDT ) ) ) VAR TempTable = FILTER ( ADDCOLUMNS ( ALLSELECTED ( dimODisp ), --needed to add the two measures to the table for filtering "RefDT", [Consec Referral_DT], --surrogate for just taking the referral date "ResetGrp", [Consec Reset_DT] ), [RefDT] <= CurDT && [ResetGrp] = ResetGroup ) VAR Consec = CALCULATE ( SUM ( dimODisp[ORecovered_N] ), TempTable ) VAR final = IF ( BeforeFail = 1, counter, Consec ) RETURN final Consec Referral_DT = MIN(factCase[Referral_DT]) Consec Reset_DT = VAR allselmin = CALCULATE ( [Consec Referral_DT], ALLSELECTED ( dimODisp[Referral_ID], dimODisp[ORecovered] ) ) VAR reset = MAXX ( CALCULATETABLE ( factCase, FILTER ( ALLSELECTED ( factCase ), factCase[Referral_DT] <= MAX ( factCase[Referral_DT] ) ), dimODisp[ORecovered] = "No" ), factCase[Referral_DT] ) RETURN COALESCE ( reset, allselmin )