How to Fix the Problem of “A circular dependency was detected”

Posted on by Summa Lai

A circular dependency is detected whenever two objects reference each other, in such a way that Power BI cannot process the objects. The details of why this happens are outlined in this article: Understanding Circular Dependencies in Tabular and PowerPivot – SQLBI. Here, the focus is solely on what to do to solve the scenario. We suggest any interested reader run through the previous article too, because understanding the scenario better is helpful in building a more reliable DAX code.

Watch this video for detail if needed.

First things first: let us state the obvious. If you create two calculated columns that actually reference each other, then you are generating a circular dependency:

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

With that said, we are confident that you did not land on this page because of such a trivial error. If that is the case however, even better: you already have your solution. Line Margin cannot be computed based on Discount Pct, if Discount Pct is computed based on Line Margin. Rephrase your code and the problem is fixed!

If your code looks correct, that is if you do not see any obvious circular dependencies, then there are two possible causes for a hidden circular dependency:

  • You are using context transition inside a calculated column.
  • You are creating a relationship that involves either a calculated column or a calculated table.

Let us start analyzing the first scenario: context transition in calculated columns. If you do not pay attention to circular dependencies, you cannot create more than one calculated column in a table – if the formula of the column contains CALCULATE anywhere. Indeed, CALCULATE in a calculated column performs a context transition and makes that column dependent on all the columns in the table. If two such columns exist, they depend on each other. Therefore, you experience circular dependency only once you have created the second column.

The correct solution to avoid this is to restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous; this is because context transition is likely to produce unexpected results.

It is worth remembering that CALCULATE is present whenever you call a measure. Consequently, any calculated column that contains a measure reference should embed the measure call inside a structure like this:

CALCULATE([measure],ALLEXCEPT(… ))

For example, if you need a calculated column in Product to store the sales amount of that product, you should enclose the measure reference inside CALCULATE/ALLEXCEPT this way:

Total Sales is a calculated column of Product

—Do NOT do this

Total Sales = [Sales Amount]

Instead, do this:

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

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

Another Article about this topic

In this article let’s understand what Circular Dependency is and how it can cause issues when creating Calculated Columns in a table that doesn’t have a Primary key.

There are always 2 types of dependencies, Regular and Circular.

Regular Dependency always exists in any kind of programming language even if you haven’t thought about that, in DAX Regular Dependencies are used to keep track of Measures, Columns, relationships etc.

For example you create a basic sales measure like the following:

Total Sales =
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

Now the DAX engines need to keep a track of the dependency created on the columns of the Sales table by SUMX and it happens automatically and you never have to worry about it but sometimes you might want to create calculated columns in a table that doesn’t have a unique key and you will notice that as soon as you confirm the code of the second column you get a Circular Dependency error, let’s understand why:

Data Model used:

Data Model.png

Sales table contains only 20 duplicated rows and Products is a regular Dimension nothing special in that to share:

The first thing to do is to create a new calculated column named “Silver Sales”

Silver Sales 1.png

The problem arises as soon as I create another calculated column with the same code:

Silver Sales 2.png

The error says: A circular dependency was detected: Sales[Column], Sales[Silver Sales], Sales[Column].

Let’s understand why we are getting Circular Dependency error.

When we wrote the code for the first column, CALCULATE starts and initiates Context Transition and since a Calculated Column is evaluated in a row context the Context Transition is performed for each row of the Sales Table. CALCULATE converts each column’s value in the currently iterated row and transforms it into an equivalent filter context.

So the code for the second line will look something like this:

CALCULATE ( 
    [Total Sales],
    Products[Color] = "Silver",

    -- Following lines of code reflect the Filter Context 
    -- created because of the Context Transition 

    Sales[ProductKey] = 610,
    Sales[CustomerKey] = 19004,
    Sales[Order Date] = DATE ( 2009, 09, 25 ),
    Sales[Quantity] = 1, 
    Sales[Net Price] = 98.1
)

And when we create the second column, internally, the code will look something like this after context transition

Silver Sales 2 = 
CALCULATE ( 
    [Total Sales],
    Products[Color] = "Silver",

    -- Following lines of code reflect the Filter Context 
    -- created because of the Context Transition
 
    Sales[ProductKey] = 610,
    Sales[CustomerKey] = 19004,
    Sales[Order Date] = DATE ( 2009, 09, 25 ),
    Sales[Quantity] = 1, 
    Sales[Net Price] = 98.1,
 
    -- The below addition is the first step in the circular dependency 
    -- as the new column now depends on the Silver Sales

    Sales[Silver Sales] = 196.2
)

So if the second column could be a part of the model, what could go wrong? The issue is if it was possible to confirm this column the first column would have depended on the second column and the second column will depend on first column because of the context transition:

And the code of the first column would have looked something like this:

Silver Sales = 
CALCULATE ( 
    [Total Sales],
    Products[Color] = "Silver",

    -- Following lines of code reflect the Filter Context 
    -- created because of the Context Transition 

    Sales[ProductKey] = 610,
    Sales[CustomerKey] = 19004,
    Sales[Order Date] = DATE ( 2009, 09, 25 ),
    Sales[Quantity] = 1, 
    Sales[Net Price] = 98.1,

    -- If the second column could be confirmed then this 
    -- column would depend on the second column and the 
    -- second column will depend on this column

    Sales[Silver Sales 2] = "Some Value"
)

Before moving to the solution let’s see if we get the same error in the Products Table which is a dimension table with a unique column.

I get no error warning in the products table after confirming the second column.

silver sales products table.png

The reason why we don’t get an error on the Dimension table and get an error in the fact table is that Products table contains a Primary Key and the Sales table doesn’t have a Primary Key, so does that mean if we add a unique column to the sales table the calculations will start working? No, its not going to happen, let’s see why.

In the below image you can see that I have added an Index column and still I get an error:

error with index.png

The reson is that having a unique column is not sufficient, the engine must know that the unique column in the primary key of the table, and one way of doing that is through the relationships.

In the data model the Products Table and Sales Table have a 1:Many relationship that’s why the engine knows that the Product Key in the Products table is the primary key and Sales has the Foreign Key.

Fixing the Issue

The way to fix the calculations is to remove the filters applied because of context transition from the both columns.

In the code what I have done is introduced REMOVEFILTERS that removes the filters that are applied because of context transition:

Silver Sales = 
CALCULATE ( 
    [Total Sales],
    Products[Color] = "Silver",
    REMOVEFILTERS ( Sales[Silver Sales 2] )
)
Silver Sales 2 = 
CALCULATE ( 
    [Total Sales],
    Products[Color] = "Silver",
    REMOVEFILTERS ( Sales[Silver Sales] )
)
REMOFILTERS.png

Deleting the Relationship

Now let’s see what happens if we break the relationship between Sales and Products table:

no relationship.png

This time I have created 2 columns in the Products table and I am basically counting the Silver Products:

Silver Products = 
CALCULATE ( 
    COUNTROWS ( Products ), 
    Products[Color] = "Silver"
)

and

Silver Products 2 = 
CALCULATE ( 
    COUNTROWS ( Products ), 
    Products[Color] = "Silver"
)
products error.png

The above image proves that just because there is a unique column in a table it doesn’t mean the calculations will work, the engine must know there is a primary key in a table:

If creating relationships isn’t an option then you can use the UI to tell the engine that the column in the table is a primary key of the table

primary key.png

And the calculations start working once again:

products working.png

Behind the Scenes

Lets’ understand what happens internally with the help of DAX query plans:

I have created a query column on the sales table using this code:

DEFINE
    COLUMN Sales[Silver Sales] =
        CALCULATE (
            [Total Sales],
            Products[Color] = "Silver"
        )
EVALUATE
    Sales
dax studio 1.png

If we take a look at the Logical Query plan you will notice that on the Line 1 it says:

Logical Query Plan 1.png

DependOnCols(1, 2, 3, 4, 5)(‘Sales'[ProductKey], ‘Sales'[CustomerKey], ‘Sales'[Order Date], ‘Sales'[Quantity], ‘Sales'[Net Price])

This is the dependency which is created because of the context transition

Now I have created another query column using the following code and have removed the dependency of Silver Sales on Silver Sales 2 otherwise the code won’t run and will produce an error

DEFINE
    COLUMN Sales[Silver Sales] =
        CALCULATE (
            [Total Sales],
            Products[Color] = "Silver",
            REMOVEFILTERS ( Sales[Silver Sales 2] )
        )
    COLUMN Sales[Silver Sales 2] =
        CALCULATE (
            [Total Sales],
            Products[Color] = "Silver"
        )
EVALUATE
    Sales

Now in the logical query plan you will see that for Sales[Silver Sales 2] we see a dependency on the Silver Sales column.

Logical Query Plan 2.png

Conclusion

When creating columns in a table that doesn’t have a Primary Key, remember to remove the cicular dependency error by using CALCULATE modifiers such as ALL/ALLEXCEPT/ALLNOBLANKROW/REMOVEFILTERS.

Ref: https://community.powerbi.com/t5/Community-Blog/Circular-Dependency-between-Calculated-Columns-in-a-Table-in-DAX/ba-p/1877267