Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
66 | |
34 | |
26 | |
22 |
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |