The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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!
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!
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)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |