Recursion Power BI Problem

Question

I am trying to come up with a DAX measure that works like a for/while loop. The calculation process is shown below. Basically this rollup calculation will stop when Previous Rollup = Current Rollup. If it is impractical to use this condition, I think about a for loop with about 10 iterations. I did research on the forum for for/while loop problems, but none of them refers back to previous iteration. 😢 Please help 😢

Rollup1 = [UpdatedEOM]-CALCULATE(SUM('Sample2'[UpdatedEOM]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))

Rollup2 = [UpdatedEOM]-CALCULATE(SUM([Rollup1]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))

Rollup3 = [UpdatedEOM]-CALCULATE(SUM([Rollup2]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))

Rollup4 = [UpdatedEOM]-CALCULATE(SUM([Rollup3]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))

Rollup5 = [UpdatedEOM]-CALCULATE(SUM([Rollup4]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))

ashish_mathur · Answer

Hi,
Share some data, explain the question and show the expected result.

evedang · Answer

Thank&nbsp;Ashish_Mathur&nbsp;for your response. For further explaination, I have the table below, with Facility, UpdatedEOM and Horizontal Facility are raw columns. Rollup 1-7 are calculated columns. For each Facility, its rollup = UpdatedEOM - Sum of rollup from other facilities that have it as "Horizontal Facility". For example, Rollup2 of AL = UpdatedEOM - SUM(Rollup1 of COT and DEV, which have AL as Horizontal Facility). The problem is these rollup depend on each other and I have to calculate multiple times until the all the current rollups = all the previous rollups (For example all Rollup6 = all Rollup7).The first three rollups are calculated below:&nbsp;Rollup1&nbsp; = [UpdatedEOM]-CALCULATE(SUM('Sample2'[UpdatedEOM]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))Rollup2 = [UpdatedEOM]-CALCULATE(SUM([Rollup1]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility])))Rollup3 = [UpdatedEOM]-CALCULATE(SUM([Rollup2]), FILTER(ALL('Sample2'),[Horizontal Facility]=MAX(Sample2[Facility]))).....&nbsp;FacilityUpdatedEOMHorizontal FacilityRollup1Rollup2Rollup3Rollup4Rollup5Rollup6Rollup7AL336,494EO16522222,074,2142,074,2142,074,2142,074,2142,074,2142,074,214CAN616,878PER-359749346,564-89,914-89,914-89,914-89,914-89,914KIN1,159UCGS11591,1591,1591,1591,1591,1591,159DES-11,511CALW-11511-11,511-11,511-11,511-11,511-11,511-11,511GLO421,992COT421992421,992421,992421,992421,992421,992421,992HER956,675CALW956675956,675956,675956,675956,675956,675956,675LUJ4,200USE42004,2004,2004,2004,2004,2004,200MIR0&nbsp;0000000MON251,213EO251213251,213251,213251,213251,213251,213251,213PEC889,727CALW-256975415,525-561,102145,211-291,267-291,267-291,267HORN55,622PER5562255,62255,62255,62255,62255,62255,622SOUTH705,084UCLS268605268,605268,605268,605268,605268,605268,605ST5471,291EO7129171,29171,29171,29171,29171,29171,291ST19179,182EO179182179,182179,182179,182179,182179,182179,182UCR70UCLS70707070707070VEL21,543EO2154321,54321,54321,54321,54321,54321,543POWDER54,809CALE5480954,80954,80954,80954,80954,80954,809PL2592,779MAN-272078-272,078-272,078-272,078-272,078-272,078-272,078PL10GLAKE0000000TOWNSEN14,018CALW1401814,01814,01814,01814,01814,01814,018LUJPEC144CALW144144144144144144144PIT361,110CALW270382274,582274,582274,582274,582274,582274,582JANE306,037CALW-434750,46350,46350,46350,46350,46350,463SALES996,978CALW19512533,403,7553,854,6254,276,6174,276,6174,276,6174,276,617BRIDGE190,751CALW190751190,751190,751190,751190,751190,751190,751ALCHE122,580JANE122580122,580122,580122,580122,580122,580122,580CALE187,803JANE132993132,993132,993132,993132,993132,993132,993CALW5,460,471&nbsp;17565442,350,081166,070691,827-436,47900KI0&nbsp;0000000COT-453,015AL-875007-875,007-875,007-875,007-875,007-875,007-875,007DEV-862,713AL-862713-862,713-862,713-862,713-862,713-862,713-862,713EO-2,834,391SALES-3694114-5,009,842-5,431,834-5,431,834-5,431,834-5,431,834-5,431,834GLAKE519,539PL2519539519,539519,539519,539519,539519,539519,539UCGS224,306CAN223147223,147223,147223,147223,147223,147223,147MAN1,880,117SALES12873382,152,1952,152,1952,152,1952,152,1952,152,1952,152,195CHEVP436,479SOUTH436479436,479436,479436,479436,479436,479436,479PER466,774PEC-205727770,90164,587501,066501,066501,066501,066UCLS752,321CAN47167483,646483,646483,646483,646483,646483,646UCRIP345,318PL2345318345,318345,318345,318345,318345,318345,318UCLEG679,928PEC679928679,928679,928679,928679,928679,928679,928USE90,727PIT8652886,52886,52886,52886,52886,52886,528&nbsp;The expected result is final rollup column having values as Rollup 7 above. I would really appreciate any advice or idea.&nbsp;FacilityUpdatedEOMHorizontal FacilityRollupAL336,494EO2,074,214CAN616,878PER-89,914KIN1,159UCGS1,159DES-11,511CALW-11,511GLO421,992COT421,992HER956,675CALW956,675LUJ4,200USE4,200MIR0&nbsp;0MON251,213EO251,213PEC889,727CALW-291,267HORN55,622PER55,622SOUTH705,084UCLS268,605ST5471,291EO71,291ST19179,182EO179,182UCR70UCLS70VEL21,543EO21,543POWDER54,809CALE54,809PL2592,779MAN-272,078PL10GLAKE0TOWNSEN14,018CALW14,018LUJPEC144CALW144PIT361,110CALW274,582JANE306,037CALW50,463SALES996,978CALW4,276,617BRIDGE190,751CALW190,751ALCHE122,580JANE122,580CALE187,803JANE132,993CALW5,460,471&nbsp;0KI0&nbsp;0COT-453,015AL-875,007DEV-862,713AL-862,713EO-2,834,391SALES-5,431,834GLAKE519,539PL2519,539UCGS224,306CAN223,147MAN1,880,117SALES2,152,195CHEVP436,479SOUTH436,479PER466,774PEC501,066UCLS752,321CAN483,646UCRIP345,318PL2345,318UCLEG679,928PEC679,928USE90,727PIT86,528

Forum Discussion

Recursion Power BI Problem

2 Replies

Related content

Recursion Strategy

Recursive hierarchy in power query

Recursion in Power BI

Power Query Underlying Problem

Recursive function in Power Query

Facility	UpdatedEOM	Horizontal Facility	Rollup1	Rollup2	Rollup3	Rollup4	Rollup5	Rollup6	Rollup7
AL	336,494	EO	1652222	2,074,214	2,074,214	2,074,214	2,074,214	2,074,214	2,074,214
CAN	616,878	PER	-359749	346,564	-89,914	-89,914	-89,914	-89,914	-89,914
KIN	1,159	UCGS	1159	1,159	1,159	1,159	1,159	1,159	1,159
DES	-11,511	CALW	-11511	-11,511	-11,511	-11,511	-11,511	-11,511	-11,511
GLO	421,992	COT	421992	421,992	421,992	421,992	421,992	421,992	421,992
HER	956,675	CALW	956675	956,675	956,675	956,675	956,675	956,675	956,675
LUJ	4,200	USE	4200	4,200	4,200	4,200	4,200	4,200	4,200
MIR	0		0	0	0	0	0	0	0
MON	251,213	EO	251213	251,213	251,213	251,213	251,213	251,213	251,213
PEC	889,727	CALW	-256975	415,525	-561,102	145,211	-291,267	-291,267	-291,267
HORN	55,622	PER	55622	55,622	55,622	55,622	55,622	55,622	55,622
SOUTH	705,084	UCLS	268605	268,605	268,605	268,605	268,605	268,605	268,605
ST54	71,291	EO	71291	71,291	71,291	71,291	71,291	71,291	71,291
ST19	179,182	EO	179182	179,182	179,182	179,182	179,182	179,182	179,182
UCR	70	UCLS	70	70	70	70	70	70	70
VEL	21,543	EO	21543	21,543	21,543	21,543	21,543	21,543	21,543
POWDER	54,809	CALE	54809	54,809	54,809	54,809	54,809	54,809	54,809
PL2	592,779	MAN	-272078	-272,078	-272,078	-272,078	-272,078	-272,078	-272,078
PL1	0	GLAKE	0	0	0	0	0	0	0
TOWNSEN	14,018	CALW	14018	14,018	14,018	14,018	14,018	14,018	14,018
LUJPEC	144	CALW	144	144	144	144	144	144	144
PIT	361,110	CALW	270382	274,582	274,582	274,582	274,582	274,582	274,582
JANE	306,037	CALW	-4347	50,463	50,463	50,463	50,463	50,463	50,463
SALES	996,978	CALW	1951253	3,403,755	3,854,625	4,276,617	4,276,617	4,276,617	4,276,617
BRIDGE	190,751	CALW	190751	190,751	190,751	190,751	190,751	190,751	190,751
ALCHE	122,580	JANE	122580	122,580	122,580	122,580	122,580	122,580	122,580
CALE	187,803	JANE	132993	132,993	132,993	132,993	132,993	132,993	132,993
CALW	5,460,471		1756544	2,350,081	166,070	691,827	-436,479	0	0
KI	0		0	0	0	0	0	0	0
COT	-453,015	AL	-875007	-875,007	-875,007	-875,007	-875,007	-875,007	-875,007
DEV	-862,713	AL	-862713	-862,713	-862,713	-862,713	-862,713	-862,713	-862,713
EO	-2,834,391	SALES	-3694114	-5,009,842	-5,431,834	-5,431,834	-5,431,834	-5,431,834	-5,431,834
GLAKE	519,539	PL2	519539	519,539	519,539	519,539	519,539	519,539	519,539
UCGS	224,306	CAN	223147	223,147	223,147	223,147	223,147	223,147	223,147
MAN	1,880,117	SALES	1287338	2,152,195	2,152,195	2,152,195	2,152,195	2,152,195	2,152,195
CHEVP	436,479	SOUTH	436479	436,479	436,479	436,479	436,479	436,479	436,479
PER	466,774	PEC	-205727	770,901	64,587	501,066	501,066	501,066	501,066
UCLS	752,321	CAN	47167	483,646	483,646	483,646	483,646	483,646	483,646
UCRIP	345,318	PL2	345318	345,318	345,318	345,318	345,318	345,318	345,318
UCLEG	679,928	PEC	679928	679,928	679,928	679,928	679,928	679,928	679,928
USE	90,727	PIT	86528	86,528	86,528	86,528	86,528	86,528	86,528