Why is this sort error still coming up??

[pborah]

Can't share data but here's a gist of the problem- 

Have a simple bar chart visual for Days Elapsed - column A grouped into custom sized buckets - B using nested IF(). Given how limited PowerBI still is in terms of it's sorting capabilities compared to tools like Tableau, I created another custom sort column - C using SWITCH(). Of course I had to get the error that says I can't use column C to sort B because there are more than one values of C for the same value of B. So I inspected my data to make sure that isn't the case. But I'm still getting the same stupid error. Is there any other reason besides non-corresponding values for which PowerBI throws this error? Thanks.

[Ritaf1983 · Accepted solution]

Hi @pborah 

The message says that you're trying to sort columns by a conflicting sort order; this happens because of duplicates in the table, which is not one-to-one.

To solve the issue, you need to create a separate table with only the sort column and the column by which you're sorting. Create a relationship between them, and then you'll be able to perform the sort.
For more detailed solution

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If my answer was helpful please give me a Kudos and accept as a Solution.

[Ritaf1983 · Accepted solution]

Hi @pborah 

The message says that you're trying to sort columns by a conflicting sort order; this happens because of duplicates in the table, which is not one-to-one.

To solve the issue, you need to create a separate table with only the sort column and the column by which you're sorting. Create a relationship between them, and then you'll be able to perform the sort.
For more detailed solution

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If my answer was helpful please give me a Kudos and accept as a Solution.

[pborah · Accepted solution]

Understood. I still maintain that for such a simple requirement i.e. sorting, Microsoft intentionally restricting the BI Analyst's options to such an overly complex approach is inefficient in terms of time and system overhead (having to create a separate table and relationships just to be able to sort). Whereas in other BI tools like Tableau, sorting is simply a matter of CTL+ hold click+drag.

I went a different route of simply giving the buckets numerical values in my nested IF() and sorting with that. Actual bucket range is in tooltips if any end user is curious. I cannot in my right mind justify the inelegant way of creating a whole separate table and relationship for this minor of a requirement. It's nice to have but not necessary even though it may make the visual look slightly amateur.

However, thank you very much for your explanation 🙏🙏🙏. Guess I just stumbled on to another one of PowerBI's quirks.