A circular dependency was detected

[timward10]

Hi,

When I move a DAX query over to the next column to repeat a formula I get the error message - A circular dependency was detected: EXENT Funnel - by Product[Assay Feb'25], EXENT Funnel - by Product[Assay Jan 25], EXENT Funnel - by Product[Assay Feb'25].

Dax query works when adding it for Jan 25 column, but a change to Feb 25 results in the above error. Can anyone advise what I can do please? Both formulas listed below.

First query - That works

Assay Count Jan'25 =
IF(
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jan 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Feb 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Mar 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Apr 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay May 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jun 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jul 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Aug 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Sep 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Oct 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Nov 24])) +
CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Dec 24])) = 12,
BLANK(),
IF(
'EXENT Funnel - by Product'[Assay Dec 24] <> BLANK(),
'EXENT Funnel - by Product'[Assay Dec 24],
IF(
'EXENT Funnel - by Product'[Assay Rev Start Date] = "Jan 25",
[Assay Revenue Split],
BLANK()
)
)
)

Second query that errors

Assay Feb 25 =

IF(

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jan 25])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Feb 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Mar 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Apr 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay May 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jun 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Jul 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Aug 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Sep 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Oct 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Nov 24])) +

CALCULATE(COUNT('EXENT Funnel - by Product'[Assay Dec 24])) = 12,

BLANK(),

IF(

'EXENT Funnel - by Product'[Assay Jan 25] <> BLANK(),

'EXENT Funnel - by Product'[Assay Jan 25],

IF(

'EXENT Funnel - by Product'[Assay Rev Start Date] = "Feb 25",

[Assay Revenue Split],

BLANK()

)

)

)

[rajendraongole1 · Accepted solution]

Hi @timward10  -Modify your measure to ensure Assay Month is correctly compared as a string

Assay Cumulative Count =
CALCULATE(
COUNTROWS('EXENT Funnel - by Product'),
FILTER(
'EXENT Funnel - by Product',
FORMAT('EXENT Funnel - by Product'[Assay Month], "MMM yy")
IN {"Jan 24", "Feb 24", "Mar 24", "Apr 24", "May 24", "Jun 24",
"Jul 24", "Aug 24", "Sep 24", "Oct 24", "Nov 24", "Dec 24"}
)
)

another options, If Assay Month is stored as a date, then compare it using YEAR() and MONTH()

Assay Cumulative Count =
CALCULATE(
COUNTROWS('EXENT Funnel - by Product'),
FILTER(
'EXENT Funnel - by Product',
YEAR('EXENT Funnel - by Product'[Assay Month]) = 2024 &&
MONTH('EXENT Funnel - by Product'[Assay Month]) IN {1,2,3,4,5,6,7,8,9,10,11,12}
)
)

Hope this helps.

[Ashish_Mathur]

Hi,

Why in the first place are you even adding data invididually for each month?  It seems that the data is not well structured.  Share some data, explain the question and show the expected result.

[rohit1991 · Accepted solution]

Hi @timward10 

A circular dependency error happens when a column or measure indirectly refers back to itself, which creates an endless loop. In your case, it’s likely because “Assay Feb 25” is built on “Assay Jan 25”, and that column already depends on another calculation, forming a cycle. The IF condition in your formula may also be pointing to fields that are tied together, which adds to the problem. To fix this, you’ll need to break the chain for example, by creating a helper table, avoiding direct references between dependent columns, or using a lookup table for date-based logic. A better option in many cases is to use measures instead of calculated columns, since measures calculate on demand and don’t create stored dependencies. You can also check the Dependency View in the Model tab to see where the loop is happening. If it’s still hard to trace, try building a simpler version of your formula first and then add complexity step by step until it works.

[timward10]

Brilliant, thank you @rohit1991 

[rajendraongole1]

Hi @timward10  - Instead of referencing calculated columns directly, create measures for reusable logic.

check the below measure:

Assay Count Prev Months =
CALCULATE(
COUNT('EXENT Funnel - by Product'[Assay Jan 24]) +
COUNT('EXENT Funnel - by Product'[Assay Feb 24]) +
COUNT('EXENT Funnel - by Product'[Assay Mar 24]) +
-- Add remaining months...
COUNT('EXENT Funnel - by Product'[Assay Dec 24])
)

Example for a  cumulative count measure as follows:

Assay Cumulative Count =
CALCULATE(
COUNTROWS('EXENT Funnel - by Product'),
FILTER(
'EXENT Funnel - by Product',
'EXENT Funnel - by Product'[Assay Month] IN {"Jan 24", "Feb 24", ..., "Dec 24"}
)
)

Then use this measure in [Assay Feb 25]

Assay Feb 25 =
IF(
[Assay Cumulative Count] = 12,
BLANK(),
IF(
NOT(ISBLANK('EXENT Funnel - by Product'[Assay Jan 25])),
'EXENT Funnel - by Product'[Assay Jan 25],
IF(
'EXENT Funnel - by Product'[Assay Rev Start Date] = "Feb 25",
[Assay Revenue Split],
BLANK()
)
)

Hope this helps.
)

[timward10]

Thanks @rajendraongole1, but when I enter the cumulative formula, I am getting the below error....

[rajendraongole1 · Accepted solution]

Hi @timward10  -Modify your measure to ensure Assay Month is correctly compared as a string

Assay Cumulative Count =
CALCULATE(
COUNTROWS('EXENT Funnel - by Product'),
FILTER(
'EXENT Funnel - by Product',
FORMAT('EXENT Funnel - by Product'[Assay Month], "MMM yy")
IN {"Jan 24", "Feb 24", "Mar 24", "Apr 24", "May 24", "Jun 24",
"Jul 24", "Aug 24", "Sep 24", "Oct 24", "Nov 24", "Dec 24"}
)
)

another options, If Assay Month is stored as a date, then compare it using YEAR() and MONTH()

Assay Cumulative Count =
CALCULATE(
COUNTROWS('EXENT Funnel - by Product'),
FILTER(
'EXENT Funnel - by Product',
YEAR('EXENT Funnel - by Product'[Assay Month]) = 2024 &&
MONTH('EXENT Funnel - by Product'[Assay Month]) IN {1,2,3,4,5,6,7,8,9,10,11,12}
)
)

Hope this helps.

[timward10]

Thank you @rajendraongole1