Circular dependency error

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?)