CIrcular Dependency with my calculated columns

dinesharivalaga · ‎11-19-2024

Hi Experts,

CIrcular Dependency errors with my calculated columns below : I need this columns for Table visual and Pie chart as well , so that i am used to create calculated column instead of measures.
A circular dependency was detected: Test Delivery Updates[_Cost Status_], Test Delivery Updates[Cost Combined], Test Delivery Updates[_Cost Status_].

DAX 1 (Calculated Column)

Cost Combined = // this DAX is for combining 2 same accounts when it occurs and return the higher risk colors on both

VAR A = 'Test Delivery Updates'[Account Name]

VAR B = ADDCOLUMNS(SUMMARIZE(FILTER('Test Delivery Updates','Test Delivery Updates'[Account Name]=A),'Test Delivery Updates'[_Cost Status_]),"Cost",SWITCH('Test Delivery Updates'[_Cost Status_],"Red",3,"Amber",2,"Green",1,0))

RETURN MAXX(TOPN(1,B,[Cost]),'Test Delivery Updates'[_Cost Status_])

DAX 2 (Calculated Column)

_Cost Status_ =

VAR CostSpentYTD = 'Test Delivery Updates'[Cost spent]

VAR PlannedCostYTD = 'Test Delivery Updates'[Planned cost]

VAR OverallBudgetedCost = 'Test Delivery Updates'[Overall budgeted cost]

VAR WhatIsStillNeeded = 'Test Delivery Updates'[What is still needed to complete the project?]

VAR IsBudgetOverrun = 'Test Delivery Updates'[Is the budget overrun due to customer dependency/CG side?]

VAR IsCRRaised = 'Test Delivery Updates'[Is the CR raised for this additional cost?]

VAR IsCRApproved = 'Test Delivery Updates'[Is the CR approved for this additional cost?]

VAR DipInMarginText = 'Test Delivery Updates'[Is there a dip in margin?]

VAR DipInMargin = VALUE(SUBSTITUTE(DipInMarginText, "%", "")) // Convert percentage text to number

VAR IsSOWSigned = 'Test Delivery Updates'[Is the SOW signed?]

VAR OutstandingInvoices = 'Test Delivery Updates'[Is there any outstanding invoices to be raised?]

VAR YTDActualFTE = 'Test Delivery Updates'[YTD Actual FTE Release]

VAR YTDPlanFTE = 'Test Delivery Updates'[YTD Planned FTE Release]

VAR YTDActualprocess = 'Test Delivery Updates'[YTD Actual process deployed count]

VAR YTDPlanprocess = 'Test Delivery Updates'[YTD Planned Process deployed count]

VAR catchup = 'Test Delivery Updates'[Is there a catch up plan available?]

VAR PercentageOver1 = DIVIDE(CostSpentYTD - PlannedCostYTD, PlannedCostYTD, 0)

VAR Status1 =

IF(

PercentageOver1 > 0.1, "Red",

IF(

PercentageOver1 < 0.1, "Amber",

"Green"

)

VAR TotalCostNeeded = CostSpentYTD + WhatIsStillNeeded

VAR PercentageOver2 = DIVIDE(TotalCostNeeded - OverallBudgetedCost, OverallBudgetedCost, 0)

VAR Status2 =

IF(

PercentageOver2 > 0.1, "Red",

IF(

PercentageOver2 > 0 && PercentageOver2 <= 0.1, "Amber",

"Green"

)

VAR Status3 =

SWITCH(

TRUE(),

IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",

IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = BLANK(), "Red",IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = "No","Amber"

)

VAR FinalStatus =

SWITCH(

TRUE(),

YTDActualFTE <> BLANK() && YTDPlanFTE <> BLANK(),IF(ABS(YTDActualFTE - YTDPlanFTE) > YTDPlanFTE * 0.1, "Red",IF(ABS(YTDActualFTE - YTDPlanFTE) <= YTDPlanFTE * 0.1 && YTDActualFTE <> YTDPlanFTE , "Amber",IF(YTDActualFTE = YTDPlanFTE, "Green"))),

YTDActualprocess <> BLANK() && YTDPlanprocess <> BLANK(),IF(ABS(YTDActualprocess - YTDPlanprocess) > YTDPlanprocess * 0.1 && catchup = BLANK(), "Red",IF(ABS(YTDActualprocess - YTDPlanprocess) <= YTDPlanprocess * 0.1 && YTDActualprocess <> YTDPlanprocess, "Amber",IF(YTDActualprocess = YTDPlanprocess, "Green",IF(catchup = "Yes","Amber",IF(catchup = "No","Red"))))),

DipInMargin <> BLANK() && IsSOWSigned <> BLANK() && OutstandingInvoices <> BLANK(),IF(DipInMargin < -3, "Red",IF(DipInMargin < 0,"Amber",IF(DipInMargin >= 0 && IsSOWSigned = BLANK() && OutstandingInvoices = BLANK(),"Green",IF(IsSOWSigned = "No" && OutstandingInvoices = BLANK(), "Amber",IF(OutstandingInvoices = "Yes", "Amber",IF(OutstandingInvoices = "No","Green")))))),

IsCRRaised = "Yes" && (Status1 = "Red" || Status2 = "Red") , Status3,

IsCRApproved = "Yes", "Green",

IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red",

IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",

IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red"), "Red",

"Green"

)

RETURN

FinalStatus

Please help to sort out this ..

Thanks

DK

Poojara_D12 · ‎11-19-2024

Hi @dinesharivalaga

The circular dependency error occurs because both of your calculated columns (Cost Combined and _Cost Status_) depend on each other either directly or indirectly, creating a loop. To resolve this, we need to adjust the approach by either breaking the dependency or replacing calculated columns with measures.

Key Changes to Resolve the Circular Dependency

Use Measures Instead of Calculated Columns
Measures are calculated at runtime and do not persist in the table, avoiding circular dependencies.
Redesign the Logic for Cost Combined
The Cost Combined column is trying to summarize and evaluate other rows dynamically, which is problematic for calculated columns. This operation is better suited for a measure.

Step-by-Step Solution

1. Replace _Cost Status_ as a Measure

Create a measure for _Cost Status_ instead of a calculated column:

_Cost Status_ Measure = 
VAR CostSpentYTD = 'Test Delivery Updates'[Cost spent]
VAR PlannedCostYTD = 'Test Delivery Updates'[Planned cost]
VAR OverallBudgetedCost = 'Test Delivery Updates'[Overall budgeted cost]
VAR WhatIsStillNeeded = 'Test Delivery Updates'[What is still needed to complete the project?]
VAR IsBudgetOverrun = 'Test Delivery Updates'[Is the budget overrun due to customer dependency/CG side?]
VAR IsCRRaised = 'Test Delivery Updates'[Is the CR raised for this additional cost?]
VAR IsCRApproved = 'Test Delivery Updates'[Is the CR approved for this additional cost?]
VAR DipInMarginText = 'Test Delivery Updates'[Is there a dip in margin?]
VAR DipInMargin = VALUE(SUBSTITUTE(DipInMarginText, "%", ""))  // Convert percentage text to number
VAR IsSOWSigned = 'Test Delivery Updates'[Is the SOW signed?]
VAR OutstandingInvoices = 'Test Delivery Updates'[Is there any outstanding invoices to be raised?]
VAR YTDActualFTE = 'Test Delivery Updates'[YTD Actual FTE Release]
VAR YTDPlanFTE = 'Test Delivery Updates'[YTD Planned FTE Release]
VAR YTDActualprocess = 'Test Delivery Updates'[YTD Actual process deployed count]
VAR YTDPlanprocess = 'Test Delivery Updates'[YTD Planned Process deployed count]
VAR catchup = 'Test Delivery Updates'[Is there a catch up plan available?]
VAR PercentageOver1 = DIVIDE(CostSpentYTD - PlannedCostYTD, PlannedCostYTD, 0)
VAR Status1 =
    IF(
        PercentageOver1 > 0.1, "Red",
        IF(
            PercentageOver1 < 0.1, "Amber",
            "Green"
        )
    )
VAR TotalCostNeeded = CostSpentYTD + WhatIsStillNeeded
VAR PercentageOver2 = DIVIDE(TotalCostNeeded - OverallBudgetedCost, OverallBudgetedCost, 0)
VAR Status2 =
    IF(
        PercentageOver2 > 0.1, "Red",
        IF(
            PercentageOver2 > 0 && PercentageOver2 <= 0.1, "Amber",
            "Green"
        )
    )
VAR Status3 =
    SWITCH(
        TRUE(),
        IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",
        IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = BLANK(), "Red", 
        IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = "No", "Amber"
    )
VAR FinalStatus =
    SWITCH(
        TRUE(),
        YTDActualFTE <> BLANK() && YTDPlanFTE <> BLANK(), IF(ABS(YTDActualFTE - YTDPlanFTE) > YTDPlanFTE * 0.1, "Red", IF(ABS(YTDActualFTE - YTDPlanFTE) <= YTDPlanFTE * 0.1 && YTDActualFTE <> YTDPlanFTE , "Amber", IF(YTDActualFTE = YTDPlanFTE, "Green"))),
        YTDActualprocess <> BLANK() && YTDPlanprocess <> BLANK(), IF(ABS(YTDActualprocess - YTDPlanprocess) > YTDPlanprocess * 0.1 && catchup = BLANK(), "Red", IF(ABS(YTDActualprocess - YTDPlanprocess) <= YTDPlanprocess * 0.1 && YTDActualprocess <> YTDPlanprocess, "Amber", IF(YTDActualprocess = YTDPlanprocess, "Green", IF(catchup = "Yes", "Amber", IF(catchup = "No", "Red"))))),
        DipInMargin <> BLANK() && IsSOWSigned <> BLANK() && OutstandingInvoices <> BLANK(), IF(DipInMargin  < -3, "Red", IF(DipInMargin < 0, "Amber", IF(DipInMargin >= 0 && IsSOWSigned = BLANK() && OutstandingInvoices = BLANK(), "Green", IF(IsSOWSigned = "No" && OutstandingInvoices = BLANK(), "Amber", IF(OutstandingInvoices = "Yes", "Amber", IF(OutstandingInvoices = "No", "Green")))))),
        IsCRRaised = "Yes" && (Status1 = "Red" || Status2 = "Red") , Status3,
        IsCRApproved = "Yes", "Green",
        IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red",
        IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",
        IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red"), "Red",
        "Green"
    )
RETURN
    FinalStatus

2. Replace Cost Combined as a Measure

Use a measure to calculate the combined cost risk status:

Cost Combined Measure = 
VAR A = 'Test Delivery Updates'[Account Name]
VAR B = 
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER('Test Delivery Updates', 'Test Delivery Updates'[Account Name] = A),
            'Test Delivery Updates'[_Cost Status_ Measure]
        ),
        "Cost", SWITCH(
            'Test Delivery Updates'[_Cost Status_ Measure],
            "Red", 3,
            "Amber", 2,
            "Green", 1,
            0
        )
    )
RETURN MAXX(B, [Cost])

Using These Measures

For Table visuals, drag the Account Name and both measures (_Cost Status_ Measure, Cost Combined Measure) into the table.
For Pie charts, drag one of the measures into the "Values" field and Account Name or another field into the "Legend" field.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

dinesharivalaga · ‎11-20-2024

@Poojara_D12 @FarhanJeelani
Thanks for your solutions 🙂

Here in my scenario , we cannot use measure as a legend in the Pie chart , that is the problem here.

If you use Account names as a legend then it will be more partitions will happen (115 accounts so far , it may increase in future)
So legend should be colors (Amber,Green or Red - Status of the each accounts) like below :

FarhanJeelani · ‎11-19-2024

Hi @dinesharivalaga,

The circular dependency error occurs because your calculated columns are interdependent. Specifically:

- `Cost Combined` depends on `_Cost Status_` through its calculation.
- `_Cost Status_` might indirectly rely on `Cost Combined` or other columns that introduce this circularity.

Why this happens:
1. Calculated columns are static row-level operations.
- They are recalculated for every row in the table, making it hard for DAX to resolve dependencies when one column's calculation references another column that depends on the first.

2. Dependency chain in DAX:
- Both columns (`_Cost Status_` and `Cost Combined`) refer to each other directly or indirectly.

Fix:
To resolve the circular dependency, you should avoid using calculated columns for this logic and replace them with measures. Measures are dynamic and calculated based on the current context, avoiding circular references.

Here’s how to rewrite the logic:

1. Convert `Cost Combined` into a Measure:

Replace the calculated column `Cost Combined` with a measure like this:

DAX

Cost Combined Measure =
VAR A = SELECTEDVALUE('Test Delivery Updates'[Account Name])
VAR B =
ADDCOLUMNS(
SUMMARIZE(
FILTER(
'Test Delivery Updates',
'Test Delivery Updates'[Account Name] = A
),
'Test Delivery Updates'[_Cost Status_]
),
"Cost",
SWITCH(
'Test Delivery Updates'[_Cost Status_],
"Red", 3,
"Amber", 2,
"Green", 1,
0
)
)
RETURN
MAXX(
TOPN(1, B, [Cost]),
'Test Delivery Updates'[_Cost Status_]
)

2. Keep `_Cost Status_` Logic in a Measure:

Convert `_Cost Status_` into a measure:

DAX

Cost Status Measure =
VAR CostSpentYTD = SUM('Test Delivery Updates'[Cost spent])
VAR PlannedCostYTD = SUM('Test Delivery Updates'[Planned cost])
VAR OverallBudgetedCost = SUM('Test Delivery Updates'[Overall budgeted cost])
VAR WhatIsStillNeeded = SUM('Test Delivery Updates'[What is still needed to complete the project?])
VAR PercentageOver1 = DIVIDE(CostSpentYTD - PlannedCostYTD, PlannedCostYTD, 0)
VAR Status1 =
IF(
PercentageOver1 > 0.1, "Red",
IF(PercentageOver1 < 0.1, "Amber", "Green")
)
VAR TotalCostNeeded = CostSpentYTD + WhatIsStillNeeded
VAR PercentageOver2 = DIVIDE(TotalCostNeeded - OverallBudgetedCost, OverallBudgetedCost, 0)
VAR Status2 =
IF(
PercentageOver2 > 0.1, "Red",
IF(PercentageOver2 > 0 && PercentageOver2 <= 0.1, "Amber", "Green")
)
VAR FinalStatus =
// Implement your complex logic here
RETURN
FinalStatus

Key Changes:
- Use Measures Instead of Calculated Columns: Measures are evaluated in the report view context, eliminating circular dependencies.
- Avoid Row Context in Measures: Use aggregations like `SUM` or `AVERAGE` instead of direct column references.
- Dynamic Filtering: Measures allow filtering and slicing dynamically in visuals like tables or pie charts.

Next Steps:
1. Replace the calculated columns in your model with the measures above.
2. Use the measures directly in your visuals (table or pie chart).
3. Test your measures to ensure they produce the same results as the calculated columns.

By using measures, you eliminate circular dependencies while preserving the functionality for your visuals.

Please mark this as solution if it helps. Appreciate Kudos.