cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Recursion Power BI Problem

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

2 REPLIES 2
Regular Visitor

Thank @Ashish_Mathur 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:

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

.....

 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

The expected result is final rollup column having values as Rollup 7 above. I would really appreciate any advice or idea.

 Facility UpdatedEOM Horizontal Facility Rollup AL 336,494 EO 2,074,214 CAN 616,878 PER -89,914 KIN 1,159 UCGS 1,159 DES -11,511 CALW -11,511 GLO 421,992 COT 421,992 HER 956,675 CALW 956,675 LUJ 4,200 USE 4,200 MIR 0 0 MON 251,213 EO 251,213 PEC 889,727 CALW -291,267 HORN 55,622 PER 55,622 SOUTH 705,084 UCLS 268,605 ST54 71,291 EO 71,291 ST19 179,182 EO 179,182 UCR 70 UCLS 70 VEL 21,543 EO 21,543 POWDER 54,809 CALE 54,809 PL2 592,779 MAN -272,078 PL1 0 GLAKE 0 TOWNSEN 14,018 CALW 14,018 LUJPEC 144 CALW 144 PIT 361,110 CALW 274,582 JANE 306,037 CALW 50,463 SALES 996,978 CALW 4,276,617 BRIDGE 190,751 CALW 190,751 ALCHE 122,580 JANE 122,580 CALE 187,803 JANE 132,993 CALW 5,460,471 0 KI 0 0 COT -453,015 AL -875,007 DEV -862,713 AL -862,713 EO -2,834,391 SALES -5,431,834 GLAKE 519,539 PL2 519,539 UCGS 224,306 CAN 223,147 MAN 1,880,117 SALES 2,152,195 CHEVP 436,479 SOUTH 436,479 PER 466,774 PEC 501,066 UCLS 752,321 CAN 483,646 UCRIP 345,318 PL2 345,318 UCLEG 679,928 PEC 679,928 USE 90,727 PIT 86,528
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors