sorting lots of categories with unknown amount

[Pbiuserr]

Hello,
I have really huge dataset and I want to sort categories. I know the pattern is Category 1, Category 2 etc so Category N where N is a number. I want them sort them from Category1 up to last category. The number of categories might be changed due to refresh of the data (there could be more from each refresh) therefore creating new table with cat number and index is not feasible way (not to mention that there could be like hundreds of categories)

Is there any feasible way to do that?

[amitchandak]

@Pbiuserr , Usually we use +0 to measure or use the option show Item with no data. But they are more  successful when you have separate dimension

[Pbiuserr]

Hi @amitchandak . I want them on slicer in that organized way. would it work as well?

[amitchandak]

@Pbiuserr , for that you need a sort column, Again you have to create a new column if you need , may be 2 two avoid circular dependency

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Pbiuserr]

This guy, luckily has categories in a good order from 1-5, however mines are like 118 and then 9 and then back to 100+ etc. I know how to sort dates but not how category which are text type but pattern is Category N (where N is a number)

[amitchandak]

@Pbiuserr , if you can have calculated column, I would split that in power query by space OR digits and non digits into a new column and would have used that as sort order

[Pbiuserr]

@amitchandak that was my first thing I've done - by M also by DAX, but in both scenarios I have circucal dependency. Thats why I am asking there in a thread if that is impossible