topic sort column in descending character + increasing number in DAX Commands and Tips

sort column in descending character + increasing number

Anonymous — Sun, 24 Apr 2022 08:04:50 GMT

I want to Sort my Job level in Descending order + increasing number such as S1,S2,S3,P1,P2,P3,P4,P5........

Re: sort column in descending character + increasing number

Migasuke — Sun, 24 Apr 2022 08:47:05 GMT

Hi Anonymous ,

Question here is, if the number of job categories can change or there might be some additional one, like X, Y etc..

In case you know, that the number of categories is final, I would recommend you to make a custom mapping table, where you define the order - just like S1 = 1, S2 = 2...and then set up, that job categories will be sort out by this new table, where you have ordering.

In case you need to have more dynamic approach I would see as the easiest solution (depends on the model) to use ordering in power query and adding the index column, which can later help as sort out column.

Step 1. Create column for letter (S,E,P...)
2. Create column with related numbers (1,2,3)
3. Set up ordering on letters descdending
4. Set up ordering on numbers asceding
5. Add index column
6. Close and load and use your index column for sorting by.

Result should look like this:

Re: sort column in descending character + increasing number

tamerj1 — Sun, 24 Apr 2022 08:47:59 GMT

Anonymous

I assume it is allways one letter one digit number combination. Then you can creat a new calculated column

Index =

RANX(Table, LEFT(Table[Code],1))*10 + RIGHT(Table[Code],1)

Re: sort column in descending character + increasing number

Anonymous — Sun, 24 Apr 2022 11:35:58 GMT

Custom Mapping table trick worked and here how it looks like now.

Though, I have one more query and that is....

I want all these 4 charts into single chart . Is there any solution for it ?

Re: sort column in descending character + increasing number

Migasuke — Mon, 25 Apr 2022 10:47:08 GMT

Hi,

Having them all in one chart has several solutions.
1. Put all measures in the single visuals (so you would see 8 columns per category)
2. Use filter or bookmark, where you switch from one measure to another one. Basically you will not see all values at once, but would be definitely more eye appealing.
3. Use small multiples, but that will look pretty much like what you have now.

One note, usually try raise a new question as a new topic, it is easier for other people to find what you were asking.

Have a nice day,

Milan.