I have a fact table with a list of lead response times. I created a calculated column which assigned each lead response time into a category (0-5 minutes, 5-15 minutes, 15-30 minutes, etc).
When I place the column into a slicer in order to filter leads by their response time range, the categories in the filter appear out of order. Here’s what the slicer looks like:
I created a calculated index column within the lead fact table, which assigned each response time range category a number from 1-6 (blanks were assigned 1). However, when I click on the measure in the fields column and try to sort the response time range column by the index columnm, I get the following error:
I tried creating a 2-column table with the category values in one column and the index values in a second column, however I wasn’t able to use that separate table when I selected “sort by column”.
Can you please upload the working of your PBIX file? So that members of our forum can assist you in a better and efficient manner and check where the “Circular Dependency” error lies and provide you the results accordingly.
In your case, Power BI does not allow to have two calculated columns that contain measures that are also based on that table. In order to understand why, you’d need a better understanding of what’s going on under the hood.
Not sure, why use of “Multiple IF’s” statements when the same formula or expression can be written with the use of “SWITCH( TRUE() )” function and same results can be generated in an efficient manner where formula is more readable and most importantly understandable while writing this type of logic.
I’m providing few of the links of the videos as well as articles.
Circular dependencies ocurred for me in my challenge #8 preparations, and I solved them by not using a calculated column in another calculated column; rather, I used the same “base” column for both the calculation and its sort. Here’s an example I threw together.
Hi @pete.langlois, we’ve noticed that no response has been received from you since the 24th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
You nailed it. Initially I had created an index column (let’s call it column B) which referenced the base column (column A). Then I tried to use column B to sort column A, but since they were referencing one another, this seemed to create the circular dependency. But when I created a new index column B with a measure that didn’t reference column A, I was able to then use the new column B as the sort by column for column A.
