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!
dkernen This is Cthulhu: Cthulhu - Microsoft Power BI Community
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, what is the actual triggering event for when the counter should reset? I can't figure that out from looking at things. Is it when the Organ Outcome changes?
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?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 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.