Forum Discussion

kmax's avatar
kmax
Helper I
2 years ago
Solved

Circular dependency error

Hello,  I am facing a strange issue when creating a report. Here are the details.   There are two main slicers. 1- Year 2- LoB Here are four main measures. 1- OB Initial = this is the openi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,kmax 

    Regarding the issue you raised, my solution is as follows:

    1.Based on the pbix file you provided, we infer that the reason you have a circular dependency problem is because you referenced [Ending CARR Measure] in your [OB Adjusted] measure, However, [OB Adjusted] is cited in [Ending CARR Measure], resulting in cyclic dependence.

    Solved: Recursion in DAX - Microsoft Fabric Community

     

    2.You can try doing the calculation in excel first and then importing it into power bi. This is by far the easiest way.

    Here are links to the relevant documents:

    How to create recursive functions in Excel with LAMBDA (spreadsheetweb.com)

     

    3.You can also implement this functionality in powerquery, but you need to put a little more effort into it:

    Here are links to the relevant documents:

    Recursive Functions in Power Query | Power BI Data (enterprisedna.co)

     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

kmax's avatar
kmax
Helper I
2 years ago

Hi  v-linyulu-msft ,

Here is the updated PBIX.
https://drive.google.com/file/d/1Wk4WppXQGYzpSMcIpRiqgYMB0l9TGEHz/view?usp=sharing

Still facing issues but I have managed to get the value of previous month as the next month opening balance.

I have found two ways to tackle circular dependancy. 

 

Option 1 - using calculated column

The ending Balance column is working fine till 4th month.
From 5th month, the ending balance is showing wrong value.
2024-03
Ending Balance column = 1893980
2024-04
Month sale column = -65471

2024-04 Ending balance =

1893980-65471=1828509 (correct value)
but it is showing 2055416.
if we subtract
1828509-2055416 = -226907 (this is month sale value of 2024-01.
same pattern continues for rest of the months of any year.

 

Option 2 - using measures
from 2nd month onward, the Opening Balance adjusted value is correctly fetching from closing balance measure value of previous month.
but the closing balance measure is showing wrong answer for all months.
it is doing
opening balance + monthly variation = closing balance measure
this is fine for 1st month. From 2nd month, it should set the opening balance value from closing balance of previous month but it is using the 1st month value of opening balance for all months.

 

I have set Option 1 and Option 2 titles for both matrix for ease in the new PBIX.
Now, I am facing issue in getting the correct sum using both the options.

Thanks