Circular dependency error

[stephenbeech]

Hello - seeking assistance with a Circular Depedency error below. 

I am creating a YTD calculated columns using DAX.

The first calculated column computes withougt error: 

YTD_#RegSubFIN =

VAR max_year =

CALCULATE(

    MAX(Base2[Period(format)]),

    ALL(Base2[Period(format)])

    )

RETURN

CALCULATE(

    SUM(Base2[# Regular Submission (Finance)]),

    Base2[Period(format)]=max_year

)

The second YTD calculated column produces an error, though the scripts appear unrelated (?):

YTD_#RegSubNF =

VAR max_year2 =

CALCULATE(

    MAX(Base2[Period(format)]),

    ALL(Base2[Period(format)])

    )

RETURN

CALCULATE(

    SUM(Base2[# Regular Submission (Finance)]),

    Base2[Period(format)]=max_year2

)

Any suggestions as to how to correct this are much appreciated! TIA!.

[Anonymous]

Hi @stephenbeech 

It appears that you have encountered a cyclic dependency error when calculating the DAX for a year-to-date (YTD) column.

This typically occurs when two or more calculated columns or metrics indirectly reference each other, creating loops that cannot be resolved.

From the code snippet you provided, it appears that both calculated columns reference the same column and use a similar pattern to calculate and .

However, without knowing the full context of the data model and the relationship between the tables, it is difficult to determine the exact cause of the circular dependency.

Here are some of the suggestions I've provided for you to try out:

Make sure there are no circular references in the relationships between tables.

Break down complex calculations into simpler intermediate steps. This sometimes helps to determine where circular references occur.

Consider using metrics instead of calculated columns; metrics are calculated at query time and can often avoid circular dependencies.

Circular dependencies can also come from the filter context of the CALCULATE application. Make sure that the filters you apply do not create loops.

Regards,

Nono Chen

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

[Wilson_]

Hi Stephen,

The reason you're getting a circular dependency error is because of a tricky thing in Power BI known as context transition. I will explain the error message you're getting first, then give you the good news on how to re-write your DAX.

The short answer is you cannot use CALCULATE in two separate calculated columns in the same table. The longer answer is since you're adding a calculated column to a table, you are necessarily in a row context where Power BI goes through your table row by row for your calculated column. However, when you use a CALCULATE function within your row context, Power BI automatically does something known as context transition, which requires the calculation to know the value of every column in the table. However, the problem is you have two calculated columns, both using CALCULATE and therefore invoking context transition. Put more explicitly, your YTD_#RegSubFIN column and your YTD_#RegSubNF column both need to know the value of the other in order for your columns to calculate properly. Hopefully you can see how that's logically impossible; you cannot have two columns whose calculation depends on each other. This article by SQLBI might help provide further details about your error that I left out of my explanation.

The good news is you can re-write your calculated columns differently to avoid this error.

For example, YTD_#RegSubFIN can be re-written as:

YTD_#RegSubFIN =
VAR max_year = MAX ( Base2[Period(format)] )
VAR filtered =
FILTER (
    Base2,
    Base2[Period(format)] = max_year
)

RETURN
SUMX (
    filtered,
    Base2[# Regular Submission (Finance)]
)

At this point though, I'm a little confused because it looks like your two calculated columns are exactly the same, except for the name of the max year variable. Anyway, hopefully that helps. Let me know if you need any further clarification. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)