Forum Discussion
Using results from previous row & Eliminating a Circular Dependency
I have tried many different ways to read and use a value from a previous row, but all of them gives me the same message... "A circular dependency was detected. In a nutshell, I have a table with different sizes of batteries (Table Battery" and another one with the operation of the battery over the time (Table Operation). The idea is to display how the State of Charge of the battery varies over a period of time.
There are two slices where I select the battery size and the Initial State of Charge to evaluate the operation.
The circular reference issue is detected on "SOC Initial" because it depends on the previous value on SOC END, which is calculated from the formula SOC End = SOC Initial + kW/Battery (kWh).
An illustration of what would expect is shown below.
I would really appreciate a support on this matter.
Circular Reference
Xiao,
I found this video from MyOnlineTrainingHub.
https://www.youtube.com/watch?v=7kFZw8zimCU&t=461s
Mynda Treacy explains how to create a running total to handle very large dataset in powerquery.
The example of Running Total with with buffer works pretty well.
I do need to adapt my model since the slicers will not be effective. I believe I can combine Power BI and PowerApps to change the inputs and recalculate the running total accordingly.
Thanks for the support!
4 Replies
- AlexisOlson
Super User
You can't do recursion in DAX. However, there are workarounds in cases where you can use cumulative totals.
Cumulative totals might work for your case. Try something like this:
SOC End = VAR InitSOC = SELECTEDVALUE ( Slicer2[SOC] ) VAR CurrID = MAX ( Table1[ID] ) RETURN InitSOC + CALCULATE ( SUM ( Table1[kW] ), Table1[ID] <= CurrID )For the SOC Initial column in your table, same DAX as above but use "<" instead of "<=".
Alexis, Thanks for your answer.
I did try the cumulative totals approach before; however, it only works when the data set is relatively small.
My dataset considers a measurement every 15 minutes during a year, so I have 35,040 rows (365 days x 24 hours x 4 measurement/hour).
I can create the measure; however, when I use it in a visual I get an error message "Can't Display the visual".
Any other idea to consider?
v-xiaotangCommunity Support
Hi itamarlopes
What does it show when you click details on the error prompt?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Xiao,
I found this video from MyOnlineTrainingHub.
https://www.youtube.com/watch?v=7kFZw8zimCU&t=461s
Mynda Treacy explains how to create a running total to handle very large dataset in powerquery.
The example of Running Total with with buffer works pretty well.
I do need to adapt my model since the slicers will not be effective. I believe I can combine Power BI and PowerApps to change the inputs and recalculate the running total accordingly.
Thanks for the support!
