Sort categories in a slicer by an index column

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:

response rate filter

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:

sort by column 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”.

Thanks for your help!

Pete
sort by column error

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

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.

Thanks and Warm Regards,
Harsh

Hi @pete.langlois,

As @Harsh suggested if you upload a sample working pbix file it will be really helpful to know what’s happing with the code.

Also you can watch the below video from sqlbi which explain about circular dependency.

@pete.langlois

This is a good refresher on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

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.

1 Like

Hello @pete.langlois,

To get going, you can try out the below formula in your file for the reference to create a calculated column for sorting purpose -

Sort Order =
SWITCH( TRUE(
	FactTableName(Response Time Range) = BLANK() , 1 , 
	FactTableName(Response Time Range) = "0-5 Minutes" , 2 , 
	FactTableName(Response Time Range) = "5-15 Minutes" , 3 ,
	FactTableName(Response Time Range) = "15-30 Minutes" , 4 ,  
	FactTableName(Response Time Range) = "30-60 Minutes" , 5 ,
	FactTableName(Response Time Range) = "61+ Minutes" , 6 ,  
7 ) 

If this doesn’t solve the problem, then please attach the PBIX file of the working.

Please Note: Please incorporate the appropriate naming conventions in your formula since it will tend to differ.

Hoping you find this useful and helps you to solve your problem. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

3 Likes

Hi, @pete.langlois really liked your post. Usually when I have this kind of problem I usually solve it with an IF function. Find the idea below.

Sort Order =

If (FactTableName(Response Time Range) = BLANK(), 1,
If (FactTableName(Response Time Range) = “0-5 Minutes” , 2 ,
If (FactTableName(Response Time Range) = “5-15 Minutes” , 3 ,
If (FactTableName(Response Time Range) = “15-30 Minutes” , 4 ,
If (FactTableName(Response Time Range) = “30-60 Minutes” , 5 , 6 ) ) ) ) )

Best Regards

Gifted

1 Like

Hello @Gifted,

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.

Hoping this helps you to understand better. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

https://dax.guide/switch/#

https://docs.microsoft.com/en-us/dax/switch-function-dax

Hi @pete.langlois.

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.

Here’s the main calculation:

Response Time Range = 
SWITCH( TRUE(),
    DimLeads[Response Time] <= 5, "0-5 minutes",
    DimLeads[Response Time] <= 15, "5-15 minutes",
    DimLeads[Response Time] <= 30, "15-30 minutes",
    DimLeads[Response Time] <= 60, "30-60 minutes",
    "61+ minutes"
)

Here’s the sort calculation:

Response Time Range Sort = 
SWITCH( TRUE(),
    DimLeads[Response Time] <= 5, 1,
    DimLeads[Response Time] <= 15, 2,
    DimLeads[Response Time] <= 30, 3,
    DimLeads[Response Time] <= 60, 4,
    5
)

Then I sorted the main calculation column by the sort column.

Hope this helps.

Greg
eDNA Forum - Circular Dependencies.pbix (18.4 KB)

3 Likes

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!

Hey @Greg,

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.

Thank you,
Pete