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

Get 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

Reply
evedang
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
evedang
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

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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