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: This time I have created 2 columns in the Products table and I am basically counting the Silver Products: Now let's see what happens if we break the relationship between Sales and Products table:
In the code what I have done is introduced REMOVEFILTERS that removes the filters that are applied because of context transition: The way to fix the calculations is to remove the filters applied because of context transition from the both columns. 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. 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 below image you can see that I have added an Index column and still I get an error: 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. I get no error warning in the products table after confirming the second column. second column will depend on this columnīefore 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. column would depend on the second column and the If the second column could be confirmed then this 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:Īnd the code of the first column would have looked something like this:
as the new column now depends on the Silver Sales The below addition is the first step in the circular dependency Sales = DATE ( 2009, 09, 25 ),Īnd when we create the second column, internally, the code will look something like this after context transition
created because of the Context Transition Following lines of code reflect the Filter Context So the code for the second line will look something like this: CALCULATE converts each column's value in the currently iterated row and transforms it into an equivalent filter context. 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. Let's understand why we are getting Circular Dependency error. The error says: A circular dependency was detected: Sales, Sales, Sales. The problem arises as soon as I create another calculated column with the same code: The first thing to do is to create a new calculated column named "Silver Sales"
Sales table contains only 20 duplicated rows and Products is a regular Dimension nothing special in that to share: 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: 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.įor example you create a basic sales measure like the following: There are always 2 types of dependencies, Regular and Circular. 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.