Forum Discussion

dkernen's avatar
dkernen
Resolver II
4 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    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)
        )