Circular Dependency between Calculated Columns in a Table in DAX

[AntrikshSharma]

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:

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"

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

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.

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:

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] )
)

Deleting the Relationship

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

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"
)

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

And the calculations start working once again:

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

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

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.

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.