Forum Discussion
Consecutive Successes in a Time Frame
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) )The final solution for this was posted as a Quick Measure Gallery Entry, Bride of Cthulhu!
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-fevSBXEmiELhsXOvoA?e=47eTwH
I would really appreciate your expertise.
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.