Consecutive Successes in a Time Frame

[dkernen]

I have the strangest problem.  I am trying to count the number of consecutive successes.

Outcome	Consec		Outcome	Consec
Success	1		Fail	0
Success	2		Success	1
Success	3		Fail	0
Fail	0		Fail	0
Success	1		Success	1

Whenever it starts with a failure - it works fine.  When it starts with a success, I am having erratic results until the first failure.  After the first failure, it works great.  But here are some examples of what I am experiencing:
Sometimes when it starts with a success, it works great

One time it should count 1, 2, 3 but it is returning 1,1, 2
Two times it should count 1, 2, 0 but it is returning 1, 1, 0 

One time it should count 1, 2, 3, 4, 5, 6 but it is returning 1, 2, 2, 3, 3, 4

I have no idea what is going wrong.  I have attached a sample file from which I have whittled down the columns in the tables.  Here is the DAX that is going awry.

Consecutive Donors =

VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])

VAR ResetGroup = [Consecutive Donors Reset_DT]

VAR TempTable =

FILTER(

ADDCOLUMNS(

ALLSELECTED(dimODisp), --needed to add the two measures to the table for filtering

"RefDT",[Referral_DT Measure], --surrogate for just taking the referral date

"ResetGrp",[Consecutive Donors Reset_DT]

),

[RefDT] <= CurDT && [ResetGrp] = ResetGroup

)

VAR Consec = CALCULATE(SUM(dimODisp[ORecovered_N]),TempTable)

RETURN Consec

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/Eb5EH7AtrKRFiyajN7EqsIkBDBL9Z8SbIbt-o...

++++Addition++++
If I do not exclude any columns in Power Query, then I get a different problem, albeit at least consistent.  In this scenario, again the ones that start with a failure work perfectly.  If they start with a success, the count does not increment until the first failure.  So I am getting

Outcome	Consec	Rather than	Outcome	Consec
Success	1		Success	1
Success	1		Success	2
Success	1		Success	3

Here is the one without removed columns

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EX7yjZeTPA5DqzEDiZXQBVkBwW1-fevSBXEmi...
Any thoughts?  I am completely at a loss.  Thank you.

[Greg_Deckler · Accepted solution]

@dkernen What about this:

Recovery for Count = 
VAR __referralDT = [Referral_DT Measure]
VAR __group = MAX('dimODisp'[ORecovered_N])
VAR __tmpTable1 = FILTER(ALLSELECTED('dimODisp'),[Referral_DT]<=__referralDT)
VAR __tmpTable1a = ADDCOLUMNS(__tmpTable1,"__Index",COUNTROWS(FILTER(__tmpTable1,[Referral_DT]<=EARLIER([Referral_DT]))))
VAR __tmpTable1b = FILTER(__tmpTable1a,[ORecovered_N] = __group)
VAR __tmpTable2 = 
    ADDCOLUMNS(
        __tmpTable1b,
        "__diff",[__Index] - MAXX(FILTER(__tmpTable1b,[__Index]<EARLIER([__Index])),[__Index]))
VAR __max = MAXX(__tmpTable2,[__Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[__Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[__Index]>=__maxStart)
RETURN
    SWITCH(TRUE(),
        __group = 0,0,
        ISBLANK(__max),1,
        __max=__maxStart,1,
        COUNTROWS(__tmpTable3)
    )

[Greg_Deckler · Accepted solution]

The final solution for this was posted as a Quick Measure Gallery Entry, Bride of Cthulhu!

[Greg_Deckler]

@dkernen This is Cthulhu: Cthulhu - Microsoft Power BI Community

[dkernen]

@Greg_Deckler 
Greg - this is incredible.  However, I am summing a field to make a running sum rather than using an index and I am still stuck.  It is still incorrect before the first "failure."

I commented my DAX to match your heroic Cthulhu.  Would you be willing to look at my specific example?
https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EX7yjZeTPA5DqzEDiZXQBVkBwW1-fevSBXEmi... 

I would really appreciate your expertise.

[Greg_Deckler]

@dkernen OK, I *think* I got this. Now I remember why I named this thing Cthulhu!!

Recovery for Count = 
VAR __referralDT = [Referral_DT Measure]
VAR __group = MAX('dimODisp'[OOutcome])
VAR __tmpTable1 = FILTER(ALLSELECTED('dimODisp'),[Referral_DT]<=__referralDT)
VAR __tmpTable1a = ADDCOLUMNS(__tmpTable1,"__Index",COUNTROWS(FILTER(__tmpTable1,[Referral_DT]<=EARLIER([Referral_DT]))))
VAR __tmpTable1b = FILTER(__tmpTable1a,[OOutcome] = __group)
VAR __tmpTable2 = 
    ADDCOLUMNS(
        __tmpTable1b,
        "__diff",[__Index] - MAXX(FILTER(__tmpTable1b,[__Index]<EARLIER([__Index])),[__Index]))
VAR __max = MAXX(__tmpTable2,[__Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[__Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[__Index]>=__maxStart)
RETURN
    IF(ISBLANK(__max),1,IF(__max=__maxStart,1,COUNTROWS(__tmpTable3)))

I added a column to your dimODisp table. PBIX is attached below signature. The major issue here is that the original had an Index column that was consequetive for what was being analyzed. Your version slices the data so the index column has to be "invented" as part of the calculation. Took me a minute to figure that out.

[Greg_Deckler]

@dkernen OK, what is the actual triggering event for when the counter should reset? I can't figure that out from looking at things. Is it when the Organ Outcome changes?

[dkernen]

@Greg_Deckler 
Not exactly.   I created a measure for the Consecutive Donors Reset_DT, which is the "group."  That group measure is working as expected.  A "success" is a recovered donor (which has two different Organ Outcomes).  A failure is a decline (which has 10 different outcomes).  Basically, when the ORecovered_N=1 then that is the indicator of "success" and I wanted those as a running sum that resets.  It is so strange to me that it worked after the first failure, but not before the first failure.  It's not a counter, it is a running sum that resets to zero when there is a failure.  We are counting the number of successes since the last failure.  So if there are three failures at the beginning then a succes, it would count 0-0-0-1.  If we have two successes then a failure it would be 1-2-0.  Is that more clear?

[Greg_Deckler · Accepted solution]

@dkernen What about this:

Recovery for Count = 
VAR __referralDT = [Referral_DT Measure]
VAR __group = MAX('dimODisp'[ORecovered_N])
VAR __tmpTable1 = FILTER(ALLSELECTED('dimODisp'),[Referral_DT]<=__referralDT)
VAR __tmpTable1a = ADDCOLUMNS(__tmpTable1,"__Index",COUNTROWS(FILTER(__tmpTable1,[Referral_DT]<=EARLIER([Referral_DT]))))
VAR __tmpTable1b = FILTER(__tmpTable1a,[ORecovered_N] = __group)
VAR __tmpTable2 = 
    ADDCOLUMNS(
        __tmpTable1b,
        "__diff",[__Index] - MAXX(FILTER(__tmpTable1b,[__Index]<EARLIER([__Index])),[__Index]))
VAR __max = MAXX(__tmpTable2,[__Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[__Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[__Index]>=__maxStart)
RETURN
    SWITCH(TRUE(),
        __group = 0,0,
        ISBLANK(__max),1,
        __max=__maxStart,1,
        COUNTROWS(__tmpTable3)
    )