Sorting characters after numbers

[ivan_xd]

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?

[v-gizhi-msft]

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:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[ivan_xd]

@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...

[v-gizhi-msft]

Hi,

Please try to change the original measure to this:

Measure = SUM('Table'[Sales])*0.5+0

Then 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:

pbix 

Best Regards,

Giotto Zhi

[ivan_xd]

@v-gizhi-msft I'm not sure what you did. But, did you replace dashes with zeros and then format zero to be a dash? I don't think that will work when my measure takes negative values. I would love to have a table sorted natively like this: 

10

4

0

-12

-19435

-

-

I guess it is not possible to customize the default order.

[v-gizhi-msft]

Hi,

I think there is no other easier solution expect my first reply.

Best Regards,

Giotto Zhi

[Greg_Deckler]

Use a different character for "-" that comes after numerals, like "A" or ":" or "="

[ivan_xd]

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.