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!
