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!
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: