cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paranoodle Regular Visitor
Regular Visitor

Is it possible to handling custom sorting cleanly on calculated columns without extra tables?

I find myself using custom sorting quite frequently, especially to display categories in the correct order on visuals, and I was wondering if there were better/more options than what I'm currently aware of. So far I've been using one of four "methods":

 

Method 1: just rename fields with leading numbers

Pros: Doesn't require extra tables or columns, Cons: Looks really messy and doesn't scale

I guess this is the easiest brute-force method, and it technically works for small amounts of categories, but I find it really hinders readability in a lot of cases, especially when the category names also involve numbers.

 

Method 2: if/else in Power Query

Pros: Doesn't require extra tables, Cons: Doesn't work with calculated columns/tables

I use this option whenever possible because I find it cleanest, but it's a no-go for any calculated columns/tables, which I frequently have to use.

 

Method 3: sort by Tooltip

Pros: Super easy and doesn't require extra tables, Cons: Clutters up tooltips and doesn't work everywhere

If tooltips aren't going to be in use much or at all, then I use this since it's fast and clean, but it doesn't work at all for legends or tables/matrices.

 

Method 4: separate sort table

Pros: Works with calculated columns/tables, Cons: Adds an extra table to keep track of

This is what I've been using whenever the other two aren't available, and it works, but on larger reports when I have 10+ tables of data and need to sort 10+ calculated columns, I find it completely impossible to keep the Data and Relationships tabs readable. It gets even worse when I have some columns that would use the same sorting order, but are indirectly linked already, so I need to have several identical extra tables, and I need to keep track of which column uses which one.

 

I don't know if I'm missing something obvious here, whether it's a solution I haven't thought of, or just that I shouldn't be trying this in the first place and there's a better way to design reports altogether. Most of the time when I have to use this, it's with pre-defined categories, or manually splitting data into buckets for easier visualization.

 

Thank you for reading this and I hope there's a good suggestion I've just missed!

1 REPLY 1
v-danhe-msft Super Contributor
Super Contributor

Re: Is it possible to handling custom sorting cleanly on calculated columns without extra tables?

Hi @paranoodle ,

Based on my research, it seemed that your methods are very detailed and could be used for many situations, but they do have different disadvantages. Power BI could just use these methods to custom sort columns currently, it they do not meet your all requirements, I suggest you could submit an idea in idea forum:

https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 267 members 2,317 guests
Please welcome our newest community members: