Is it possible to manually sort/order columns in this visual?

[Mk60]

I wonder if I can manually change the order of the columns in this visual, (can't use default options high to low, or alphabetically order them). I would need to order columns explicitely based on the X axis descriptions, so column 1 should be first from the left, next would be column 2, next 3 and so forth? I would much apprecite any suggestion. Please advise! Thank you!

[hnguy71 · Accepted solution]

Hi @Mk60 

Yes that's possible. You could create a sorting table for this sort of purpose. Here's an example:

Then select the category column and click 'Sort by Column' and then select the sort id:

And all that's left is to use the field from your sorting table. Make sure you have a relationship between the two tables based on your category. 

[hnguy71 · Accepted solution]

Hi @Mk60 

Yes that's possible. You could create a sorting table for this sort of purpose. Here's an example:

Then select the category column and click 'Sort by Column' and then select the sort id:

And all that's left is to use the field from your sorting table. Make sure you have a relationship between the two tables based on your category. 

[Deku]

Yes. If you add a extra column to the same table as the axis label and give each item a interger value 1,2,3... Based on the order you want. You can then set the order by for your label column, based off that new column

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

[Mk60]

My axis label is actually anoter calculated column. Would you mind to include example of the dax I would use to get those integer values you're suggesting here. Thanks much, Deku!

[Deku]

create a calculated column

sortBy =
SWITCH(
   table[axis label],
   "below 1:1", 1,
   "1.25-1.00", 2,
  ...
)

[Mk60]

Any suggestion on this error?

[Deku]

can't mix integer (1,2,3) with text ("unknown"). Replace unknown with 9999

[Mk60]

So using 9999 in Dax worked, thank you for that tip. Now, I am not sure how to add this new sorting column to the existing visual? If I add it to the existing X-axis metric it does add a second row of values on my X-axis, which I do not want? If I remove my existing metric it does not work? What am I not doing right here. 

[danextian]

Hi @Mk60 

You don't need to add the sorting column to the visual. You need to custom sort DSC Bucket by SortBy column you created.

Note: you may not be able to custom sort successfully due to a circular dependency error - SortBy referencing DSC Bucket and DSC Bucket being sorted by SortBy. Either create SortBy column in the query editor using the add Conditional Column feature or duplicate the DSC Bucket column and sort the duplicate instead and use in in the visual.

[Mk60]

Hi danextian

I did get the same circular dependency error as you mentioned when I tried your first suggestion. So I tried creating new table as suggested abowe, and was finally able to make it work for me here, but I am very interested in your option without new table, which I would prefer. I never used Conditional column option, (nor creating duplicate column), so I would love to explore those as well since I have few more metrics with the same sorting issue. Would you mind to provide some tips on using conditional column to solve this issue? So much apprecite your expertise, and time to help. That a lot!

[Deku · Accepted solution]

Please see @hnguy71 post, he has the image for where you apply the sort by