- Ciao, friends.
Did it ever happen to you to try to write some code,
like a calculated column or a calculated table,
try to build a relationship
and hit the circular dependency error?
It's an unpleasant situation
because whenever you have a circular dependency,
it's very hard to understand exactly
from where the problem comes from.
Luckily, there are only three possible scenarios
where you might find a circular dependency.
And in this video, we are going
to analyze the three scenarios.
I want to keep the video very practical,
so I will not go through all the detailed information
about why a circular dependency happen.
If you are interested in the why,
then we have an article at SQLBI
that we are linking below in the description
of the video, and you can go further and read it.
Here we mainly focus on understanding briefly
what a circular dependency error is
and the scenario where you might find it
and how to solve that.
Let's get started.
The first scenario that we are going to analyze require us
to create a calculated column in a table.
And this calculated column needs
to have CALCULATE inside the formula.
In order to show you the demo,
I need a table that is unrelated
to the remaining part of the model.
So what I did is creating a table
that I named best products.
Best products contains the best products in my company.
And it's a calculated table that as you see
has no relationship with the remaining part of the model.
It's not important to look at the code
of best products for now.
It's important to look that I created a calculated column
in this calculated table that uses CALCULATE.
Number of camera products uses CALCULATE
to compute the number of products
where the product name contains the word camera.
There's nothing bad with this code, it works just fine.
It's neither the best or the fastest way
to compute this expression.
It's just a demo to show you the circular dependency.
This column works, but what happens
if I try to build another column with the very same code?
Right now, I copied the code of the column.
I create a new column.
I just paste the code
and I call it camera products number two.
As soon as I hit Enter, you see I'm going
to hit the problem of circular dependency.
Instead of creating the column,
I get a circular dependency was detected
between best product number of camera products,
best products column
and best products number of camera products.
One of the problems of circular dependency
is that it's hard to understand where the problem is.
Here the problem is in the dependency list.
Indeed, camera products depends
on all the columns of the table.
And the same for camera products two.
So this expression does not depend only on the product name,
because of the presence of CALCULATE,
it depends from all the columns in the table.
So camera products depends on camera products two
and camera products two in turn depends on camera products.
If you want to fix this problem,
what you need to do is reduce the dependency list
of the column so that it includes only the needed column
and in our case, the needed column is only the key
of the table, the product key.
So what I'm going to do is make this column depend only
on the product key.
And I can obtain this goal by using ALLEXCEPT
best products, ALLEXCEPT best products,
and I only keep the product key.
As a consequence, this column will now depend only
on the product key and if I hit Enter,
you see that I can compute the column.
It's a best practice to use this pattern everywhere
whenever you reference a measure
or whenever you use CALCULATE in your code.
So both camera products and camera products two,
they both should contain ALLEXCEPT.
If you use the same code in a table
that is the target of a relationship,
a dimension in a data model, for example,
you will not see the error,
and the reason is, Power BI has specific optimizations
that kick in and reduce automatically the dependency list.
This works if you do that in a dimension
but it's the result of an optimization.
By changing the code, by making the code more complex,
for example, using USERELATIONSHIP
or when you work with composite models,
this optimization does not kick in.
That is why it's always a best practice
whenever you use CALCULATE in a calculated column
to use ALLEXCEPT to reduce the dependency list
to only the columns that are actually needed.
Now, this was the first scenario
where you might have a circular dependency
in calculated columns.
But you might have circular dependency also
with calculated tables as soon as you try
to build relationships between a table in the model
and the calculated table.
That dependency does not depend on CALCULATE,
instead it depends on the presence of the blank row.
Again, we are not going to explain the details.
I just want to show you how to solve it.
We need to spend some time understanding this code
because now it is relevant.
This table computes the products
that are in the top 5% of Contoso products.
So it places a filter for the brand to be Contoso.
Then computes the value of sales that places a product
in the top 5%, and finally, it retrieves
from all the products the ones
where the sales amount is greater than the minimum sales.
Out of the product table,
I only retrieve the product key and the product name.
And the result, as you see, is a table
that contains the product key and the product name
for only the products which are Contoso and in the top 5%.
This table works fine and I have it here.
Now, look what happens if I try to build a relationship.
If I try to build a relationship
between product key and product key this way,
everything works just fine, I can do that.
But if I try to build the relationship the other way around,
so from product key to product key,
I obtain a circular dependency error.
It says that a circular dependency was detected
and then the error message is really hard to understand
because it uses codes that are nearly impossible
to understand at first glance.
The problem this time is the dependency of the blank row.
If I were able to build the relationship,
the product table might contain the blank row or not,
depending on the value of best products.
And best products, in turn, depends on product
because the way I computed it.
So in order to solve this problem,
I need to break the dependency on the blank row.
And in order to break this dependency,
what I need to do is replace any function
that references the blank row
with another function that does not use the blank row.
ALL references the blank row, ALLNOBLANKROW does not.
DISTINCT does not reference the blank row, VALUES does.
So we need to replace all the instance of ALL
with ALLNOBLANKROW, and all the instances
of VALUES with DISTINCT and that will be enough
to fix the problem.
Let's do that.
If we look at this code, we have ALL that is used here.
And we can get rid of it and place ALLNOBLANKROW.
This will not be enough
because if I try to build the relationship again
after having replaced ALLNOBLANKROW,
you see that it still does not work.
If I build the relationship this way,
I still have my circular dependency error.
There is still an ALL very well hidden in my code.
And even though you look at the code
and you will find ALL nowhere,
you need to remember that this filter,
product brand Contoso is actually expanded by DAX.
This becomes FILTER ALL product brand
where product brand equals Contoso.
You write a simple condition but actually,
this is the code that the engine executes.
And now you see ALL here.
We need, instead of using all, to use ALLNOBLANKROW.
Now my code does not contain references
to the blank row anywhere
and I can build a relationship the right way.
I can go ahead and now the relationship is fine.
Actually, I don't want a one-to-one,
I want a many-to-one, regular relationship
and that works.
As you have seen, we had to replace ALL with ALLNOBLANKROW
in order to make the relationship work.
Now, a very similar scenario happens
whenever you try to build a relationship
not with a calculated table
but based on a calculated column.
Also in this case, you need to replace any reference to ALL
with ALLNOBLANKROW and you need to worry
about the presence of the blank row.
Let's see that with a demo.
In order to show you that, I created a table
that contains price ranges.
I want to slice my product by price ranges.
So I have a table with only three rows
that contains the price range key,
the range, the minimum and the maximum value.
Now, this table right now has no relationship
with the remaining part of the model.
You see that if I place it here,
it has no relationship with the product table.
What I want to do, is build a relationship
between product and price ranges
and to do that, I created a price range key column
in the product table that is the base of my relationship.
What does price range key do?
Well, it takes the current product unit price
and then searches for the price range key in price ranges
where the minimum and maximum values are...
less or equal and greater than the current price.
This column computes a correct number.
You can see it here.
I have the price range key computed in my product table
and now I want to use this column
to build the relationship.
Unfortunately, this is not going to work.
If I try to build the relationship,
I still hit the problem of the circular dependency.
Same error as before and the reason is the very same
but now this problem is in the calculated column.
We need to search for references of VALUES
and get rid of them and we have one here.
But before doing that, we are using CALCULATE.
Now, CALCULATE forces context transition.
So let's get rid of context transition first
because we don't want,
this CALCULATE is useful only to place these two filters.
It does not rely on context transition.
So we remove the filter from the product
and then we replace any reference to VALUES with DISTINCT.
We already know this is not enough
because we have two hidden ALL here.
Remember this price ranges min value
less than the current price?
This is actually FILTER ALL price ranges min value.
We need to replace this exactly as we did earlier.
So this becomes a FILTER ALLNOBLANKROW
of price ranges min value
where the min value is less than the current price.
This comma is not needed.
And the same way, I need to do a FILTER
ALLNOBLANKROW of price ranges max value
where the max value is greater than the current price.
If I did everything correctly,
now this code is much longer than before
but I get rid of all the dependencies from the blank row.
And I if now go here, I can create the relationship
between the product table and the price ranges table.
That allows me to slice by price range my product table.
As you have seen, whenever you have a circular dependency,
this has two simple causes.
If you're working with calculated columns,
the most likely cause is that you are using CALCULATE
in your calculated column
and the dependency list is much wider than expected.
Therefore, what you need to do
is use ALLEXCEPT to get rid of all the dependencies,
except the one on the primary key.
If you're working with relationship, on the other hand,
then the circular dependency comes
from the blank row and you need to get rid
of dependencies on the blank row
by using ALLNOBLANKROW instead of ALL,
DISTINCT instead of VALUES,
and applying REMOVEFILTERS whenever needed.
Doing this simple modification lets you work
without circular dependencies.
Remember, if you want to understand more about the topic
and if you want to go really deeper
and reach the next level, we have an article at SQLBI.com
that you can read in order to understand all the details.
Enjoy DAX.