Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sort with DAX Function FORMAT

Hello All.   Does anyone know when the bug will be fixed for sorting measure values that have the FORMAT() function applied to them?  
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Wait, what bug is that? FORMAT changes things to text. So if for some reason you are trying to sort something in numerical order that would be a problem. Not certain if that is a bug.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you all for the responses.

     

    I have a switch that goes bewteen Count and Sum. 

     

    when I format for currency it wont sort 😞

     

    Switch (Customer Value) = SWITCH(MAX('(Switch) KPI'[ID]),1,[Switch (Count)],2,FORMAT([Customer (Total Sales)],"$#,##0.00"))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous,

      I'd like to suggest you create a calculated column and setting 'sort by column' property to use the original numeric field as sort order, it can fix the 'sorting issue' text value sorting order.

      In addition, you can also try to use the table properties formatting feature to change your measure format instead of use format function, this feature does not change the type of formula result.

      Regards,

      Xiaoxin Sheng

       

      • d_gosbell's avatar
        d_gosbell
        Super User
        6 years ago
        Anonymous wrote:

        Hi Anonymous,

        I'd like to suggest you create a calculated column and setting 'sort by column' property to use the original numeric field as sort order, it can fix the 'sorting issue' text value sorting order.

        In addition, you can also try to use the table properties formatting feature to change your measure format instead of use format function, this feature does not change the type of formula result.

         

        Anonymous If you read the response from Anonymous  just above your reply you will see that neither of these options will work. Roberto456 is trying to dynamically change the formatting based on the filter context so neither a calc column or the measure properties will work for this. Unfortunately there is no good solution for this at the moment other than maybe changing the structure of the report to see if you can make use of separate measures instead of doing a switch on the KPI id.