The perfect technical post (in my view) is the heroic story about how someone encountered a challenge and – through late-night toiling – eventually found the solution. In this case, I was struggling with a circular dependency in a report last week. But first things first, what is circular dependency in Power Bi?
“A circular dependency is detected whenever two objects reference each other, in such a way that Power Bi cannot process the objects. Often you will make a calculated column when you, for example, want to add certain categories to certain values.”
The example I want to bring up here is the variable “tenure,” which is divided in several categories:
- People that have been with the company for 5 to 10 years get the label “5-10 years”
- People that have been with the company 10 to 20 years get the label “10-20 years”
- People that have been with the company for more than 20 years get the label “20 years+”
My goal was to show this in a graph together with the headcount. The DAX formula for my categories seems straightforward:
Plotting the graph should have been a piece of cake. Except, I wasn’t able to sort my new categories. The sorting was done based on the first number of my label, which was the wrong order as you can see in the picture below.
My first idea was to make a second calculated column that referenced to my “range” column to sort it. However, this is exactly what caused the circular dependency error. After some unfruitful reading on the ‘Microsoft Power Bi community’, I tried my luck reading up on some SQLBI courses. It was here our famous Alberto Ferrari suddenly explained an exercise with this exact same issue. The solution was simple, yet elegant:
Et voilà, we created another calculated column that is not dependent on the previous calculated column while it does allow to sort our range column. Hope this post helps people who are stuck in the same situation I was!
