Forum Discussion
Sorting characters after numbers
I have a measure that returns either a number or a "-" character. I have put this measure in a table visual and sorted it in descending order. It shows at the top the rows with measure="-" and then all the numbers in descending order. What can I do to have the "-" values at the bottom, and the numbers at the top in descending order?
7 Replies
Use a different character for "-" that comes after numerals, like "A" or ":" or "="
Sadly that doesn't work. My measure does some calculation, if it fails it returns a character. No matter what character I use, it always is "greater" than any other number.
Hi,
According to your description, i create a sample to test:
I create a measure first:
Measure = IF(MAX('Table'[Sales])<>BLANK(),SUM('Table'[Sales])*0.5,"-")When i want to sort this measure in descending order, it shows the issue as you:
So for this issue, please try this rank measure:
rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)It shows:
Then sort this rank measure in in descending order, the result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
v-gizhi-msft Thank you for your answer. Very clear. So, I asume there is no other solution? No hack to have Power Bi see a character as "lesser" than any numeric figure? I didn't want to have to tell the user: "Hey, you want to sort this one measure of your table? well too bad.... Wait! No, NO... don't click that, you're gonna see a bunch of dashes... you have to sort this other column right here if you want that other one sorted, because PBI does weird things sometimes. Oh, and for that other column... yeah, you see, each column has a special column if you want it sorted"
Oh, well...
Hi,
Please try to change the original measure to this:
Measure = SUM('Table'[Sales])*0.5+0Then enter this to measure custom format in Model Pane:
#,#.00;#,#.00;"-"Sort the table by this measure, it shows:
Tips: If you have more than one specifical character like '-', this workaround can not take effect.
And i think there is no other easier solution to solve this issue.
Here is my changed pbix file:
Best Regards,
Giotto Zhi
