Forum Discussion

DanErickson's avatar
DanErickson
Frequent Visitor
2 years ago
Solved

Circular Dependency Issue

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!
Need Help
  • DanErickson's avatar
    DanErickson
    2 years ago

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • DanErickson's avatar
    DanErickson
    Frequent Visitor
    2 years ago

    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.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        DanErickson
        Frequent Visitor
        2 years ago

        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!