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.

OutcomeConsec OutcomeConsec
Success1 Fail0
Success2 Success1
Success3 Fail0
Fail0 Fail0
Success1 Success1

 

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-o7YxB0_YcA?e=1Rdqn0

++++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

OutcomeConsecRather thanOutcomeConsec
Success1 Success1
Success1 Success2
Success1 Success3

Here is the one without removed columns

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

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)
    )

     

7 Replies