Why DAX Says Circular Dependency When Nothing Is Circular

You add a calculated column. It works. You add a second one that looks nothing like the first, and Power BI refuses the whole model with "A circular dependency was detected."

Read both formulas again and there is no cycle. Neither column mentions the other. The error is still correct, because DAX is not tracking the columns you named. It is tracking the columns your formula silently depends on, and CALCULATE expands that list to the entire table.

Key takeaways#

• CALCULATE in a calculated column performs context transition, which filters the table by every column of the current row.
• A formula that mentions one column therefore depends on all of them, including columns that do not exist yet.
• Two such columns in one table depend on each other. That is the cycle.
• The fix is ALLEXCEPT with the table's primary key, which narrows the dependency back to one column.
• Measures are immune, because there is no row context to transition from.

What does "circular dependency" actually mean in DAX?#

DAX builds a dependency graph before it evaluates anything, and it refuses any model where that graph contains a loop. The graph is built from column-level dependencies, not from the text of your formula. A single CALCULATE can add every column in the table to a column's dependency list.

That last sentence is the whole problem. Without it, the error message reads as a bug.

Why does CALCULATE make a column depend on the whole table?#

Because CALCULATE invoked inside a row context performs context transition: it converts the current row into a filter on every column of that table. The engine cannot know in advance which of those filters matter, so it records a dependency on all of them.

SQLBI's worked example is the clearest way to see it. This column looks like it touches one field:

Test1 =
CALCULATE (
    COUNTROWS ( 'BestProducts' ),
    BestProducts[Product Name] >= "Contoso"
)

Context transition rewrites it into something closer to this:

CALCULATE (
    COUNTROWS ( 'BestProducts' ),
    BestProducts[ProductKey] = 1084,
    BestProducts[Product Name] = "Contoso SLR Camera...",
    -- and a filter for every other column in the table
)

Now add a second calculated column, Test2, to the same table using the same pattern. Test1's expanded filter list includes Test2, because Test2 is a column of the table. Test2's list includes Test1. Neither formula names the other and the cycle is real.

The CALCULATE you did not write#

CALCULATE is present whenever you call a measure, which is SQLBI's point and the reason this error catches people who never typed the function. A calculated column that does nothing but reference a measure carries an invisible CALCULATE, and therefore an invisible dependency on every column in the table.

This column looks harmless:

Total Sales = [Sales Amount]

It is the same construct as the Test1 example above. The measure reference wraps itself in CALCULATE, context transition fires, and the column now depends on the whole table. Add a second column of the same shape and the model breaks.

The fix has the same shape as the explicit case:

Total Sales =
CALCULATE ( [Sales Amount], ALLEXCEPT ( 'Product', 'Product'[ProductKey] ) )

Writing the CALCULATE out is not redundancy. It is what gives you somewhere to put the ALLEXCEPT.

How do I fix a circular dependency error?#

Restrict what the context transition is allowed to filter. Wrap the table in ALLEXCEPT and keep only the primary key, so the column depends on one field instead of all of them. SQLBI's guidance is to "use ALLEXCEPT in order to remove any filter apart from the primary key."

Test1 =
CALCULATE (
    COUNTROWS ( 'BestProducts' ),
    BestProducts[Product Name] >= "Contoso",
    ALLEXCEPT ( 'BestProducts', BestProducts[ProductKey] )
)

The dependency graph now records Test1 → ProductKey and nothing else. A second column written the same way records Test2 → ProductKey. No edge exists between them, so no cycle exists either.

REMOVEFILTERS works the same way and reads better to some teams; SQLBI names both. The choice is stylistic, and the requirement is identical: name the primary key and nothing else.

Two conditions have to hold for this to work:

1. The table needs a real primary key, one column that uniquely identifies a row. Without it, ALLEXCEPT cannot narrow the filter to a single field.
2. The key must not itself be calculated. A calculated primary key drags its own dependencies into the graph and you are back where you started.

Should this be a measure instead?#

Usually, yes. A measure is evaluated at query time in filter context, so there is no row context to transition from, and no expanded filter list to create dependencies. Most calculated columns that hit this error were reaching for something a measure does natively.

Which one you want comes down to what the value is for.

A calculated column is computed once at refresh and stored in the model, which costs memory in every partition. A measure computes on demand and stores nothing. When both would work, the measure is the cheaper answer, and it cannot produce this error.

The reasoning is the same one that governs SQL query cost: what a formula reads matters more than how it is written, and the reads are the part the text hides.

When the logic belongs in Power Query instead#

Some columns are genuinely row-level, genuinely static, and just complicated. Bucketing a value into bands, parsing a code out of a string, normalising a category name: none of that needs the DAX engine at all.

Power Query runs before the model is built, so a column produced there arrives as ordinary data with no dependency edges. It is immune to circular dependency by construction. The trade-off is that the value is frozen at refresh time and cannot respond to anything the user selects.

One rough rule sorts most cases. If the formula would give the same answer for a row no matter what is on screen, it belongs in Power Query. If the answer changes with the selection, it belongs in a measure. Calculated columns are for the narrow middle, where a stored per-row value has to exist for slicing or for a relationship.

Two other causes worth ruling out#

Not every circular dependency traces back to CALCULATE. Two more sources come up often enough to check before rewriting anything.

Chained calculated columns. Column A reads column B, which reads A back. This one is a genuine cycle in the text, and no ALLEXCEPT will help. SQLBI's example is two lines that are individually reasonable:

Line Margin = Sales[Line Amount] - Sales[Discount Pct]
Discount Pct = DIVIDE ( Sales[Line Margin], Sales[Line Amount] )

Each formula defines itself in terms of the other. The fix is to break the definition, not to narrow it: pick whichever of the two is the source of truth, compute it from raw columns only, and derive the second from the first. Chains of three or more calculated columns in one table are worth flattening on principle, because each link is another edge in the graph.

Bidirectional relationships. A filter travelling both ways between two tables can close a loop that neither table's formulas reveal. Set the relationship to single direction unless something specifically needs the reverse path, and check cross-filter direction on every relationship touching the tables in the error.

Matching the symptom to the cause#

The error text is the same in every case, so the useful diagnostic is where and when it appeared rather than what it says.

The last row is the one that wastes the most time. Dependencies are model-wide, so the table named in the error is often not the table you were working in.

Reading the formula that broke#

In practice, the measures where this happens are rarely two lines long. A production CALCULATE with nested VAR blocks, three filter arguments and a SWITCH is hard to read as one line, and one line is how it usually arrives from a colleague or a deployment script.

Formatting it first is not cosmetic. Once VAR, RETURN and each filter argument sit on their own lines, the CALCULATE calls are countable and you can see which ones are inside a row context. The DAX formatter does that in the browser, which matters here because a measure encodes how your organisation defines revenue or margin along with your internal table names. The DAX minifier reverses it when the definition has to travel back into a script, and the Power Query formatter covers the M side if you move the logic upstream.

Debugging it in order#

1. Format the formula so every CALCULATE is visible.
2. Count the CALCULATE calls in a row context. Each one is a candidate.
3. Check for a usable primary key on the table named in the error.
4. Add ALLEXCEPT with that key to the outermost CALCULATE and retest.
5. If there is no key, move the logic to a measure or to Power Query.
6. Change one thing, then refresh. Two edits at once and you cannot tell which one worked.

Step 6 is the one people skip. The dependency graph is global, so a partial fix can move the error to a different column and read as a new problem.

The short version#

"Circular dependency" in DAX is almost never a cycle you wrote. It is CALCULATE performing context transition inside a calculated column, quietly expanding a one-column dependency into a whole-table one, until two such columns reference each other through the table itself.

Narrow it with ALLEXCEPT on the primary key when you need the column. Move it to a measure when you do not. And before either, format the formula, because the CALCULATE causing it is usually buried in a line nobody has read end to end.

Frequently asked questions#

Why does the error appear on a column I did not change?#

Because the dependency graph is model-wide. Your new column added an edge that closed a loop through columns that were already there, so the engine reports the cycle rather than the edit. Revert the change, confirm the model loads, then re-add it on its own.

Does ALLEXCEPT hurt performance?#

Not meaningfully in this position. It narrows the filter context that CALCULATE applies, so the engine does less work, not more. The real performance question with calculated columns is that they are materialised and stored at refresh, which costs memory whether or not ALLEXCEPT is present.

Can I use ALLEXCEPT without a primary key?#

No. ALLEXCEPT narrows the dependency to the columns you name, and if no single column uniquely identifies a row, there is nothing to narrow it to. Add a surrogate key in Power Query, or move the logic into a measure.

Why do measures never hit this error?#

A measure is evaluated in filter context at query time, so there is no row context for CALCULATE to transition from. Without context transition there is no expanded filter list, and without that list there are no whole-table dependencies to collide.

Should I just move everything to Power Query?#

No. Power Query values are frozen at refresh and cannot respond to a selection, so anything that should change with a slicer belongs in DAX. Move logic upstream when the answer is genuinely per-row and static, which is most string parsing and most bucketing.

Related reading#

• SQL Query Optimization — the same lesson about a query whose real cost is invisible in its text
• MongoDB Index Types
• The ESR Rule for Index Order
• Format a measure before you debug it with the DAX Formatter, collapse it for a deployment script with the DAX Minifier, and tidy the M side with the Power Query Formatter.

References#

• SQLBI: Understanding circular dependencies in DAX
• SQLBI: Avoiding circular dependency errors in DAX