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 opening balance of a year. Value is coming from a small table.

2- OB Adjusted = if column IsFirstMonth = 1, take value from [OB Initial], otherwise the Ending CARR value of previous month is the opening value of next month. e.g. if it is 2nd month, the obadjusted value will be [Ending CARR] of previous month. This is correctly getting the value of previous month as the opening value of next month.
3- Variation Ending CARR = this is the monthly sale.

4- Ending CARR =
if IsFirstMonth = 1,
[OB Initial] + [Variation Ending CARR ]
else
[OB Adjusted] + [Variation Ending CARR ]
This is where the issue is.
IF(
_IsFistMonth = 1 && SELECTEDVALUE(Activitylist[Year]) = _SelectedYear,
[OB Initial] + [Variation Ending CARR], // For the first month,
CALCULATE(
[OB Initial] + [Variation Ending CARR] // it should be [OB Adjusted] + [Variation Ending CARR ] but it is showing circular dependency error.


I have tried different ways but issue is still there.
Need help to fix this.
PBIX with dummy data can be downloaded here > https://drive.google.com/file/d/1oaD8-4JRjDGqKreyCSQjPWndl8NKwxYE/view?usp=sharing

Thanks in advance

Need Help

4 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 Lu,
      Thanks for the details.

      The requirement is to do all the calculations in PowerBI to reduce dependence on Excel. So, should I assume that this is something not possible using DAX?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,kmax 

        Thank you for your quick reply.

         

        Yes, your assumption is correct.

         

        if your requirements involve recursion, recursion cannot be achieved with dax alone.

         

        Of course, if you have other new discoveries, you are welcome to share with us.

         

        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