Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dkernen
Resolver II
Resolver II

Running 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.

IDRelated_DTCorrectConsecutive
01072021-08301/07/2021 19:44Yes1
01192021-00201/19/2021 00:20Yes2
01192021-02601/19/2021 09:41No0
01232021-08501/23/2021 22:41No0
01282021-06001/28/2021 20:40No0
02022021-06802/02/2021 22:24Yes1
02182021-05102/18/2021 16:38Yes2
03022021-01403/02/2021 07:36Yes3
03042021-03803/04/2021 13:42No0

 

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

 

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EWZK99sH8PNOt5HzxFFxP3oBGjmSEHKoF1vau...

Any hints/suggestions/measures would be most appreciated!

@running total

2 ACCEPTED SOLUTIONS
Vera_33
Resident Rockstar
Resident Rockstar

Hi @dkernen 

 

I've tried below ways, the performance is not good enough, just give you a little hint

 

Vera_33_0-1623567394832.png

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)

View solution in original post

dkernen
Resolver II
Resolver II

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!

 

View solution in original post

2 REPLIES 2
dkernen
Resolver II
Resolver II

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!

 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @dkernen 

 

I've tried below ways, the performance is not good enough, just give you a little hint

 

Vera_33_0-1623567394832.png

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.