Circular Dependency Error with SWITCH() Statement

[mckinleyallen]

Hello,

I am getting a circular dependency error when creating a conditional format based on a % and school.

I am looking to determine if our schools are in or out of compliance with different metrics. 

Example:

I have one measure that counts the number of kids for CATEGORY 1 (SUM(column1)

Another measure that counts the number of kids for CATEGORY 2 SUM(column2)

To get the % I am dividing each one of those columns by the total number of kids enrolled.

With those calculations, I have two measures

Measure1 = DIVIDE(Category1,Total_Kids_Enrolled)

Measure2 = DIVIDE(Category2, Total_Kids_Enrolled)

With those measures, I am trying to make 2 conditional formatting calculations because each school has a different compliance % 

CF_Category1 = 
SWITCH( TRUE(),

Measure1 > XX && (School_Code ="SCHOOL1" || School_Code = "SCHOOL2"), "#5F8575',

Measure1 > XX && (School_Code ="SCHOOL3" || School_Code = "SCHOOL4"), "#5F8575',

"DE6A73")

CF_Category2 =

SWITCH( TRUE(),

Measure2 > XX && (School_Code ="SCHOOL1" || School_Code = "SCHOOL2" || School_Code ="SCHOOL3"), "#5F8575',

Measure2 > XX && (School_Code = "SCHOOL4"), "#5F8575',

"DE6A73")

CF_Cateogry2 is giving me an error saying, circular dependency detective.

Can anyone help me with how to resolve this issue?

[mckinleyallen]

The separate table worked! Thank you!!

[Shravan133 · Accepted solution]

The circular dependency error you're encountering usually occurs when there’s a loop or indirect reference between measures or calculations in Power BI. This can happen if one measure depends on another in a way that creates a cycle of dependencies.

To troubleshoot and resolve the issue, follow these steps:

1. Understand the Dependency:

Circular dependencies often arise when one measure relies on another, and that measure in turn relies on the first measure or another measure that depends on it. Ensure that none of your measures are interdependent in a way that could cause a loop.

1. Check Measures for Indirect Dependencies:

Review your measures and calculations to ensure they do not indirectly reference each other in a way that creates a cycle.

1. Simplify Your Conditional Formatting Measures:

You can avoid complex logic in conditional formatting measures by simplifying them or using alternative methods. For instance, instead of directly using complex measures, you can break down the logic into simpler components.

1. Refactor Your Measures:

You may need to refactor your DAX measures to ensure there’s no interdependence causing the circular reference.

Here’s a simplified approach to your measures and conditional formatting:

Refactored Measures

Assuming Total_Kids_Enrolled is not causing issues and is a simple measure, your Measure1 and Measure2 should be straightforward:

Measure1 = DIVIDE(SUM('Table'[Category1]), [Total_Kids_Enrolled])

Measure2 = DIVIDE(SUM('Table'[Category2]), [Total_Kids_Enrolled])

Conditional Formatting Measures

Since you are applying conditional formatting based on the Measure1 and Measure2, make sure these measures do not depend on each other.

Refactor your conditional formatting logic as follows:

CF_Category1 =

SWITCH(

    TRUE(),

    [Measure1] > XX && [School_Code] IN {"SCHOOL1", "SCHOOL2"}, "#5F8575",

    [Measure1] > XX && [School_Code] IN {"SCHOOL3", "SCHOOL4"}, "#5F8575",

    "DE6A73"

)

CF_Category2 =

SWITCH(

    TRUE(),

    [Measure2] > XX && [School_Code] IN {"SCHOOL1", "SCHOOL2", "SCHOOL3"}, "#5F8575",

    [Measure2] > XX && [School_Code] = "SCHOOL4", "#5F8575",

    "DE6A73"

)

Use Alternative Methods:

1. Static Calculation: If possible, calculate compliance percentages directly in the dataset or during data import, which can simplify the logic needed in Power BI.
2. Separate Tables for Conditional Formatting: Create separate tables or calculated columns that hold compliance thresholds and use them in your conditional formatting instead of complex DAX.

Example of Separate Table Approach:

Create a table for compliance thresholds:

School_Code	Category1_Threshold	Category2_Threshold
SCHOOL1	0.8	0.7
SCHOOL2	0.75	0.65
...	...	...

Use this table to drive conditional formatting:

CF_Category1 =

SWITCH(

    TRUE(),

    [Measure1] > LOOKUPVALUE(Compliance[Category1_Threshold], Compliance[School_Code], [School_Code]), "#5F8575",

    "DE6A73"

)

CF_Category2 =

SWITCH(

    TRUE(),

    [Measure2] > LOOKUPVALUE(Compliance[Category2_Threshold], Compliance[School_Code], [School_Code]), "#5F8575",

    "DE6A73"

)

Conclusion:

• Ensure no circular references exist between measures.
• Refactor complex measures to avoid dependencies causing circular errors.
• Use simplified and alternative approaches to manage complex conditional formatting requirements.

By simplifying your DAX expressions and ensuring no circular dependencies, you should be able to resolve the issue and achieve the desired conditional formatting in your Power BI report.