Forum Discussion
dkernen
4 years agoResolver II
Consecutive Successes in a Time Frame
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 ...
- 4 years ago
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) ) - 4 years ago
The final solution for this was posted as a Quick Measure Gallery Entry, Bride of Cthulhu!
Greg_Deckler
4 years agoCommunity Champion
The final solution for this was posted as a Quick Measure Gallery Entry, Bride of Cthulhu!