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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI d_gosbell,

        Thanks for your remind. (after doing some further test with measures and 'properties formatting' feature, it seems the same as you mentioned)

        Anonymous Current power bi seems not able to achieve your requirement, perhaps you can submit an idea for your requirement to the ideas forum.

        Regards,

        Xiaoxin Sheng

         

    • Amit_Saxena's avatar
      Amit_Saxena
      Microsoft Employee
      6 years ago

      Hello Xiaoxin Sheng,

      How can i get this "table properties formatting feature" enable in power bi desktop, i can't see that?


      Thanks,

      Amit

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

         

        How can i get this "table properties formatting feature" enable in power bi desktop, i can't see that?

         

        The UI has changed a bit since this thread started. In either of the report view or data view, if you click on a numeric column (in the screenshot below I clicked on the "FromDistance" column) you will see a "column tools" ribbon appear. The formatting options are in that ribbon.