Forum Discussion
Resolver IIRunning Total with Reset
I have followed this post https://community.powerbi.com/t5/Desktop/Running-Total-Reset-based-on-Column-Attribute/m-p/251130 but my columns are in different tables.
I have a fact table with the specific needed date, a date table, and a dimension table. I want to count consecutive successes.
| ID | Related_DT | Correct | Consecutive |
| 01072021-083 | 01/07/2021 19:44 | Yes | 1 |
| 01192021-002 | 01/19/2021 00:20 | Yes | 2 |
| 01192021-026 | 01/19/2021 09:41 | No | 0 |
| 01232021-085 | 01/23/2021 22:41 | No | 0 |
| 01282021-060 | 01/28/2021 20:40 | No | 0 |
| 02022021-068 | 02/02/2021 22:24 | Yes | 1 |
| 02182021-051 | 02/18/2021 16:38 | Yes | 2 |
| 03022021-014 | 03/02/2021 07:36 | Yes | 3 |
| 03042021-038 | 03/04/2021 13:42 | No | 0 |
I am unclear how to create the MAX date (needed for the running total) since my criteria of "Yes" is in a dimension table and my dates are in my fact table. I suspect it will be close to something like this, but I am stumped because I need to use multiple tables.
Measure 4 = MAXX(FILTER(ALLSELECTED(Table4),Table4[Criteria]="Yes"&&Table4[DateMain]<=MAX(Table4[DateMain])),Table4[DateMain])
Run stock = CALCULATE(SUM(Table4[Mvnt Quantity]), DATESBETWEEN(Table4[DateMain],[Measure 4],LASTDATE(Table4[DateMain])))
Any hints/suggestions/measures would be most appreciated!
@running total
Hi dkernen
I've tried below ways, the performance is not good enough, just give you a little hint
test5 is based on your sample measure
test5 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =ADDCOLUMNS(FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT),"Y", RELATED(dimODisp[ORecovered_N])) VAR MinDT = MAXX(FILTER(T1,[Y]=0),[Referral_DT]) VAR CurY = MAX(dimODisp[ORecovered_N]) RETURN IF(CurY=1,COUNTROWS(FILTER(T1,[ReferralCall_DT]>=MinDT&&[ReferralCall_DT]<=CurDT)),0)test4 is another way, to group all 1 together along with the help of test1, test2 and group
test4 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR CurY = MAX(dimODisp[ORecovered_N]) VAR vGroup = [group] VAR T1= FILTER(ALLSELECTED(factCase),factCase[ReferralCall_DT]<=CurDT&&[group]=vGroup) RETURN IF(CurY=1,COUNTROWS(T1),0) test1 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT) RETURN COUNTROWS(T1) test2 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT) RETURN SUMX(T1,RELATED(dimODisp[ORecovered_N])) group = IF(MAX(dimODisp[ORecovered_N])=1,[test1]-[test2],-1)I wanted to follow-up to show how I made this work. I wish I could have done with by only creating two measures, but I could not figure out how to get the reset group to work within the Consecutive Count Measure.
First, I needed a measure from my fact table because I need it later:
Referral_DT Measure = MIN(factCase[Referral_DT])
Then I created a grouping for the reset. (This is the part that I wish I didn't have a separate measure.)Consecutive Reset_DT =
VAR allselmin = -- required for the initial cases before the first "No"
CALCULATE(
[Referral_DT Measure],
ALLSELECTED(dimODisp[Referral_ID],dimODisp[ORecovered])
)VAR reset =
MAXX (
CALCULATETABLE(
factCase,
FILTER(
ALLSELECTED ( factCase),
factCase[Referral_DT] <= MAX ( factCase[Referral_DT])
),
dimODisp[ORecovered]="No"
),
factCase[Referral_DT]
)RETURN COALESCE(reset,allselmin)
Then the final measure
Consecutive Count=
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR ResetGroup = [Consecutive 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 Reset_DT]
),
[RefDT] <= CurDT && [ResetGrp] = ResetGroup
)
VAR Consec = CALCULATE(SUM(dimODisp[ORecovered_N]),TempTable)
RETURN Consec
This project took me quite some time to figure out, so I hope I can save time for someone else!
2 Replies
Hi dkernen
I've tried below ways, the performance is not good enough, just give you a little hint
test5 is based on your sample measure
test5 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =ADDCOLUMNS(FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT),"Y", RELATED(dimODisp[ORecovered_N])) VAR MinDT = MAXX(FILTER(T1,[Y]=0),[Referral_DT]) VAR CurY = MAX(dimODisp[ORecovered_N]) RETURN IF(CurY=1,COUNTROWS(FILTER(T1,[ReferralCall_DT]>=MinDT&&[ReferralCall_DT]<=CurDT)),0)test4 is another way, to group all 1 together along with the help of test1, test2 and group
test4 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR CurY = MAX(dimODisp[ORecovered_N]) VAR vGroup = [group] VAR T1= FILTER(ALLSELECTED(factCase),factCase[ReferralCall_DT]<=CurDT&&[group]=vGroup) RETURN IF(CurY=1,COUNTROWS(T1),0) test1 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT) RETURN COUNTROWS(T1) test2 = VAR CurDT = SELECTEDVALUE(factCase[Referral_DT]) VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT) RETURN SUMX(T1,RELATED(dimODisp[ORecovered_N])) group = IF(MAX(dimODisp[ORecovered_N])=1,[test1]-[test2],-1)
dkernenI wanted to follow-up to show how I made this work. I wish I could have done with by only creating two measures, but I could not figure out how to get the reset group to work within the Consecutive Count Measure.
First, I needed a measure from my fact table because I need it later:
Referral_DT Measure = MIN(factCase[Referral_DT])
Then I created a grouping for the reset. (This is the part that I wish I didn't have a separate measure.)Consecutive Reset_DT =
VAR allselmin = -- required for the initial cases before the first "No"
CALCULATE(
[Referral_DT Measure],
ALLSELECTED(dimODisp[Referral_ID],dimODisp[ORecovered])
)VAR reset =
MAXX (
CALCULATETABLE(
factCase,
FILTER(
ALLSELECTED ( factCase),
factCase[Referral_DT] <= MAX ( factCase[Referral_DT])
),
dimODisp[ORecovered]="No"
),
factCase[Referral_DT]
)RETURN COALESCE(reset,allselmin)
Then the final measure
Consecutive Count=
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR ResetGroup = [Consecutive 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 Reset_DT]
),
[RefDT] <= CurDT && [ResetGrp] = ResetGroup
)
VAR Consec = CALCULATE(SUM(dimODisp[ORecovered_N]),TempTable)
RETURN Consec
This project took me quite some time to figure out, so I hope I can save time for someone else!