Forum

Forums
Public Forums - For...
Power Query
Calculated column w...
 
Notifications
Clear all

Calculated column with circular dependency errors

 
Power Query
Last Post by Velouria 4 months ago
4 Posts
2 Users
0 Reactions
288 Views
RSS
 Nancy Siner
(@nancy-siner)
Posts: 2
New Member
Topic starter
 

I'm building a calculated column that queries other columns in that table as well as measures to determine the status of a donor this year:

Status CY = SWITCH(
    TRUE,
    OR(History[Deceased]="Y",History[Do Not Contact]="Y"),"lost",
    History[First Donation Date].[Year]=2024,"first time",
    AND([PY Actual Revenue]>0,[CY Actual Revenue]>0),"retained",
    AND([PY2 Actual Revenue]>0,AND([PY Actual Revenue]=0,[CY Actual Revenue]>0)),"reengaged",
    AND([PY3 Actual Revenue]>0,AND(OR([PY2 Actual Revenue]=0,[PY Actual Revenue]=0),[CY Actual Revenue]>0)),"reengaged",
    "lapsed"
    )
The History references are data columns and the rest are measures.
 
I'm trying to build a second calculated column references all the same columns and measures, but it gives me the error "A circular dependency was detected: History[Column], History[Status CY], History[Column]"
 
Status PY = SWITCH(
    TRUE,
    OR(History[Deceased]="Y",History[Do Not Contact]="Y"),"lost",
    History[First Donation Date].[Year]=2023,"first time",
    History[First Donation Date].[Year]=2024,"not yet",
    AND([PY2 Actual Revenue]>0,[PY Actual Revenue]>0),"retained",
    AND([PY3 Actual Revenue]>0,AND([PY2 Actual Revenue]=0,[PY Actual Revenue]>0)),"reengaged",
    "lapsed"
    )
 
Is there a way to get both of these fields to work so I can create a chart comparing the number of people who are lapsed, retained, first time and reengaged donor for each year?
 
Posted : 18/12/2024 6:50 pm
 Velouria
(@debaser)
Posts: 836
Member Moderator
 

Why are you using a calculated column rather than a measure, as a matter of interest?

The problem is that your measures are being calculated in a row context, and that context is based on every column in the table. If you have two calculated columns using measures, the context of each depends on the other, so you have a circular dependency.

 
Posted : 19/12/2024 5:57 am
 Nancy Siner
(@nancy-siner)
Posts: 2
New Member
Topic starter
 

I'm using a calculated column because I knew how to create the logic there, but not in a measure. Specifically, I'm unsure how to reference these lines

OR(History[Deceased]="Y",History[Do Not Contact]="Y"),"lost",
    History[First Donation Date].[Year]=2024,"first time",
 
since those columns are data not measures. There's an "if this then this" logic that I can't wrap my mind around in a measure.
 
Posted : 20/12/2024 1:02 pm
 Velouria
(@debaser)
Posts: 836
Member Moderator
 

If you need to do it as a calculated column, you will need to figure out how to remove some of the filters to get rid of the circular dependency - e.g. using ALLEXCEPT. See if this article helps:

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

 
Posted : 20/12/2024 2:04 pm
Forum Jump:
  Previous Topic
Next Topic  
Share: