Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Satya2804
Frequent Visitor

How to avoid circular dependency using previous row

Below is a sample data - first 3 columns are my inputs. Total Supply, Match and Long are my calculated columns. 

 

Total Supply = Current Quarter Supply + Previous quarter Long. Example Total Supply Q2 = 38 + 6 = 44

Match = Min(Outlook, Total Supply). Example Match Q2 = Min (41, 44) = 41

Long = Total Supply - Match. Example Long Q2 = 44-41 = 3

 

PowerBI gives a circular dependency error but Total Supply is actually using previous quarter Long and not current quarter long, so logically it is not circular. 

 

Can anyone help replicate the measures in PowerBI?

 

QuarterSupplyOutlookTotal SupplyMatchLong
Q1312531256
Q2384144413
Q34222452223
Q44522682246
8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Do you have a proper Date column?  If yes, then share the download link of the PBI file.


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

Hi Ashish

Yeah I can add a quarter end date as below:

Quarter end dateQuarterSupplyOutlook
31-03-2023Q13125
30-06-2023Q23841
30-09-2023Q34222
31-12-2023Q44522

Hi,

That is definitely cicular logic.  I misread your question initially.


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

Well, it may be circular but List.Accumulate can handle it.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGEUamSrE60UpGIL4FkDAxBPONQUyQoJERmG8C4ptC+bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Supply = _t, Outlook = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Supply", Int64.Type}, {"Outlook", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate({1..[Quarter]},[Total=0, Match=0, Long=0],(state,current)=>[ 
    Total = #"Changed Type"{current-1}[Supply] + state[Long],
    Match = List.Min({Total,#"Changed Type"{current-1}[Outlook]}),
    Long = Total-Match ]
)),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Total", "Match", "Long"}, {"Total", "Match", "Long"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Hi,

Thank you for sharing this.


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

@Ashish_Mathur I learned how to better carry multiple variables through the iterations, and how (and when) to address them in in each cycle ( like the " state[Long] " example). I am more and more impressed by the versatility of List.Accumulate.

lbendlin
Super User
Super User

Do you need this in Power BI? As a measure or calculated column?  Or can it be done in Power Query?

Was trying to solve for it in a measure, but even if it is a PowerQuery it works..

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors