Forum Discussion
Starting and closing balances generate circular reference
Thank you again Jianbo. Although I'm not an expert on the DAX language I know that row-context is a fundamental part of the DAX language, in which case row co-ordinates are important. Whenever a measure is placed in a table it will be evaluated against each row in the table according to any defined relationships. It is not simply calculated once for the column or table.
Perhaps it would be more accurate to say that 'DAX does not evaluate row-context before deciding whether there is a circular dependency'.
If DAX were to evaluate row-context there would be no dependency between my StartingBalanceMeasure and PreviousDayClosingBal.
In any case, a colleague has suggested a different approach and it has done the job nicely. Essentially, starting and closing balance are calculated independently of each other using the VAR function. A total of 9 lines of code is required for each. Below is the starting balance calculation:
Weight (Starting Balance) =VAR vD = MAX('Date (Reporting)'[Date])Var vBalance = Calculate(SUM('Balances'[Weight]),'Balances'[Source] = "Inventory")var vProd = Calculate(SUM('Balances'[Weight]),'Balances'[ArrvlDate] < vD, 'Balances'[Source] = "Production")var vSupply = CALCULATE([Weight (Supply)], ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)var vTransOut = CALCULATE([Weight (Transfers Out)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)var vTransIn = CALCULATE([Weight (Transfers In)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)var vExpire = CALCULATE([Weight (Expire)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)return vBalance + vProd -vSupply +vTransIn- vTransOut - vExpire
Community SupportHi D_Gibson ,
Excel is cell-based, unlike excel, power bi is not cell-based, so this error occurs
If two objects depend on each other, the engine does not know which one to compute first. If A depends on B and – at the same time – B depends on A, then it is impossible to compute A nor B. As soon as you compute A, this triggers the refresh of B. So you start refreshing B, wich in turn triggers the refresh of A. You are stuck in an infinite loop with no hope of ever getting the job done.
For more details, please refer to:
Understanding circular dependencies in DAX - SQLBI
Avoiding circular dependency errors in DAX - SQLBI
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Jianbo for your response. Yes, I am aware of what a circular reference is and that Excel is cell based.
I note that your response has been marked as an accepted solution eventhough I have not endorsed it.
If you examine my code provided above you will see there is no circularity in my logic. Starting balance for period T equals closing balance for period T-1. If this is impossible to calculate it wouldn't be possible to calculate it in Excel either. You seem to be suggesting that if tool A isn't smart enough to do something it's impossible and the logic is flawed, eventhough tool B can do it very easily.
I have reviewed the article on sqlbi.com but don't believe the circumstances are the same as my use case and I don't believe the explanations will solve my error.
My question was in relation to the code in my original post. Do you have any pointers on a way to adjust the code I posted above to avoid Power BI getting upset about a circular reference ?