Circular Dependency Issue

[DanErickson]

I am creating multiple Calculated Columns in a table, so I am running into circular dependancy issues.  I have ready everything that I can on how to fix this (understanding that creating more that one calcuated column can lead to this problem), but I haven't been able to solve it.  My first calculated column (which works fine) uses the following DAX:

CC Aud 1100 Categories =

VAR __VALCC = [Cascade]

RETURN

    SWITCH (

        TRUE (),

        __VALCC <= (.4*[CC Aud Cap]), "Low",

        __VALCC <= (.6*[CC Aud Cap]), "Moderate",

        __VALCC <= (.8*[CC Aud Cap]), "Ideal",

        __VALCC <= [CC Aud Cap], "High",

        __VALCC >= [CC Aud Cap], "Over Capacity",

        BLANK ()

    )

I would like to use the same DAX, just changing the appropriate references.  I have tried putting an ALLEXCEPT in there, but I'm obviously not doing it correctly.  I would appreciate any insight!

[DanErickson]

Thank you for your response, but this is not it.  The DAX above works fine.  My problems is that I am adding Calculated Columns, and my understanding is that once you add one calculated column, any other calculated columns become dependent on that first one.  I have seen solutions using EXCEPTALL and REMOVEFILTERS, but I haven't seen how to use those functions in conjunction with the SWITCH function, which I am using above (every example I have seen is using CALCULATE).  Anyway, I would like to basically copy and paste that DAX, change the references to point to different columns and measures, but add them all in the same table.  

[v-tianyich-msft]

Hi @DanErickson ,

I think there is some misunderstanding on your part. It does depend on the first computed column, but it doesn't have a circular dependency if it doesn't affect the first computed column. You can try to use the existing expression directly, and if you have problems, please provide sample data as well as expected results.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[DanErickson]

I appreciate you sticking with me on this.  I have a screenshot below.  I have copied and pasted the DAX I used for the first calculated column, but updated the references to refer to a different column from the table and a different measure.  So it shouldn't be dependant at all on the first calculated column, but I still get the error.  I would like to have 4 calculated columns that correspond to the 4 regular columns from the table.  I should also mention that the measure that is pulled in here (CC Aud Cap or EP Aud Cap) is pulled from a different dataset entirely and in in no way dependent on any of the data in this table.

Thank you!

[v-tianyich-msft]

Hi @DanErickson ,

Based on the error message, it seems to be related to the [CC Aud 1000 Categories] in your table as well. Please provide sample data if you can.

Best regards,
Community Support Team_ Scott Chang

[DanErickson]

The [CC Aud 1100 Categories] is the first calculated column.  I agree that the error is saying it is dependent on that column, but they don't reference each other at all.  They both use the same DAX, just pointed to different references.  My understanding is that once you have one calculated column, it changes the filter context, so other calculated columns become dependent, even if there is no actual circular reference.  This is what I am trying to fix.

[DanErickson · Accepted solution]

ChatGPT just provided me with a working solution.  My final DAX looks like:

EP Aud 1100 Categories =

VAR __VALEP = [East Paris]

VAR __EP_Aud_Cap = CALCULATE([EP Aud Cap], REMOVEFILTERS(Aud1100))

RETURN

    SWITCH (

        TRUE (),

        __VALEP <= (.4*__EP_Aud_Cap), "Low",

        __VALEP <= (.6*__EP_Aud_Cap), "Moderate",

        __VALEP <= (.8*__EP_Aud_Cap), "Ideal",

        __VALEP <= __EP_Aud_Cap, "High",

        __VALEP >= __EP_Aud_Cap, "Over Capacity",

        BLANK ()

    )

[v-tianyich-msft]

Hi @DanErickson ,

Based on the DAX expression you provided, please make sure that [CC Aud Cap] does not participate in any calculations in [Cascade] seems to solve the problem. If you still have problems, please provide more information.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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