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

      • Amit_Saxena's avatar
        Amit_Saxena
        Microsoft Employee
        6 years ago

        Thanks But that doesn't allow me to format the text in refect these values in thousands, Actually i have sales values appearing in millions and these have to represent in thousand or amusing it like xyz= Format ([Measure1] ,"#,.0,K"), now format is converting this inot string and i can't sort that measure 'xyz' in table, though if i use the value function to convert this like value (Format ([Measure1] ,"#,.0,K")) then it works but there is another problem, now if i use any filter to filters the values let say based on Region then table doesn't show any data and error message says can't convert value xxxxxxK of type text to Number. Pls suggest! 

         

        Thanks,

        Amit