Forum Discussion

ivan_xd's avatar
ivan_xd
Frequent Visitor
6 years ago

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 a...
Need Help
ivan_xd's avatar
ivan_xd
Frequent Visitor
6 years ago

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's avatar
v-gizhi-msft
Community Support
6 years ago

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's avatar
    ivan_xd
    Frequent Visitor
    6 years ago

    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's avatar
      v-gizhi-msft
      Community Support
      6 years ago

      Hi,

       

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

       

      Best Regards,

      Giotto Zhi