Forum Discussion
Recursive loop Measure /Auto Adjustable Measure /Circular Dependency Issue While Adjusting values
Hi all,
I’m working on a P&L matrix in Power BI where I have:
Base values (BU P&L + Manual Adjustments)
Intercompany (IC) values
A calculated difference between IC and BU P&L
What I’m trying to achieve
My goal is:
Calculate the difference between IC and BU P&L
Apply that difference back to the Intercompany values
So that the final difference becomes 0
This logic works in Excel, where I can adjust IC values and the difference recalculates dynamically until it becomes zero.
Problem in Power BI
In Power BI, when I try to implement this:
My difference calculation depends on IC values
My IC values are being adjusted using that same difference
This creates a loop (circular dependency):
Difference depends on IC
IC depends on Difference
Power BI throws a circular dependency error and does not allow this calculation.
What I need help with
I understand that Power BI does not support iterative calculations like Excel, but I need guidance on:
How to structure this logic properly in Power BI
How to adjust Intercompany values so that the difference becomes 0
Best practice for handling this type of “feedback adjustment” or allocation logic
Whether this should be handled in:
DAX (with a different approach)
Power Query / Dataflow
Or pre-calculated outside Power BI
Key requirement
Only Intercompany values should be adjusted
Final difference should be 0
Manual adjustments and allocations must still be included
Summary (short)
I need to:
Calculate a difference
Push it into IC values
And eliminate the difference
But without creating a circular dependency in DAX.
Thanks in advance for any guidance!
DAX does not support recursions. You need to reformulate your logic so it works without these.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Gurpreetsingh1,
You are correct, looping is not possible in Power BI.
You cannot push a calculated difference back into IC in DAX due to circular dependency. Instead, solve algebraically (IC = BU), use proportional allocation, or add a plug/adjustment line to eliminate the difference.
Your logic below is creating circular dependency
Difference = IC – BU
IC (Adjusted) = IC – DifferenceYou recreate your Excel logic in Power BI by using a plug/adjustment measure (IC Adjustment) instead of feeding difference back into IC, eliminating circular dependency.
I've created a sample .pbix file for you, kindly take a look.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
4 Replies
Hi Gurpreetsingh1,
You are correct, looping is not possible in Power BI.
You cannot push a calculated difference back into IC in DAX due to circular dependency. Instead, solve algebraically (IC = BU), use proportional allocation, or add a plug/adjustment line to eliminate the difference.
Your logic below is creating circular dependency
Difference = IC – BU
IC (Adjusted) = IC – DifferenceYou recreate your Excel logic in Power BI by using a plug/adjustment measure (IC Adjustment) instead of feeding difference back into IC, eliminating circular dependency.
I've created a sample .pbix file for you, kindly take a look.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!DAX does not support recursions. You need to reformulate your logic so it works without these.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Gurpreetsingh1 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi lbendlin and grazitti_sapna , Thank you for your prompt responses.
Hi Gurpreetsingh1 , could you please try the proposed solution shared by grazitti_sapna ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
Hi Gurpreetsingh1 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
