Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
eyJrIjoiM2MwYTQ2NjQtOWEzZi00YTE4LWI5YzUtYjAzZmU4NGVjYTY5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9