cancel
Showing results for 
Search instead for 
Did you mean: 
AntrikshSharma

Circular Dependency between Calculated Columns in a Table in DAX

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

AntrikshSharma_35-1622620674774.gif

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.

Comments

This was very helpful. Thank you !

This is incredibly helpful. We were banging our heads against this and your clear explanation was just what we needed.

 

This is possibly not an uncommon scenario so documenting it here in case it helps someone else. We were using a (week) date table calculated in DAX with GenerateSeries(). This table is used to store some aggregations on ~1M rows. The aggregations were columns with SUMS for each of 10 categories. First aggregation column was ok, but adding a second column failed with the circular dependency error.

 

We had trouble getting the model view to show our date column as an option for a primary key - so that option didn't work for us.

 

Using ALLEXCEPT after the filter context worked well. Our aggregation table is called WEEK_AGG so the measure is like:

Category 0-9 =
VAR _category = "0-9"
RETURN CALCULATE(
    [total count],
    'OtherTable'[Category] = _category,
    'OtherTable'[Week] = [StartOfWeek],
    ALLEXCEPT(WEEKAGG, [StartOfWeek])
)

 

AllExcept is nice if you have multiple columns because you can "whitelist" your key column(s) rather than blacklisting your other calculated measures.

 

We also tried making another table and relating it - that worked as well.

Polls
What is your favorite Power BI feature release for May 2022?