Sorting Text Column As Extracted Numbers?

[Anonymous]

Hey all,

I have a column that looks something like this: AA1, AA2, AA10, AA12, AA20

When I sort the column however, it treats it as a text and does the following:

AA1

AA10

AA12

AA2

AA20

I need to instead order it as follows:

AA1

AA2

AA10

AA12

AA20

I am using a DirectQuery so I cannot use index columns. I also tried extracting the numbers from the column then sorting by that number column but I got a circular dependency error. The calculated column formula was :

NumExtract = if(LEFT(table[col], 2)="AA", (MID(table[col], 3, LEN(table[col])-1)), table[col])

Anyone know how I might be able to sort this column properly?

Thanks!

[v-yiruan-msft]

Hi @Anonymous ，

Please create a measure as below and apply it onto the related visual. Then sort the visual data by this measure just as below screenshot....

Measure =
RANKX (
    ALL ( 'table' ),
    CALCULATE ( MAX ( 'table'[NumExtract] ) ),
    ,
    ASC,
    DENSE
)

Best Regards 

[Anonymous · Accepted solution]

When I try and add in that measure column I get the below error. The format of the ranked column is Decimal Number though so I don't know why it would be treating it as Char or Currency.

edit: I found the solution using my num extract column! I placed it at the top of the row list in the matrix, changed the format to whole number, then sorted the matrix by that column. In order to hide it, I changed the name of the column to "." and minimized the column width.