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 ...
Need Help
  • 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)
    )

     

dkernen's avatar
dkernen
Resolver II
4 years ago

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's avatar
Greg_Deckler
Community Champion
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)
    )