Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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])))
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 |
Hi,
Share some data, explain the question and show the expected result.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |