Hello All. Does anyone know when the bug will be fixed for sorting measure values that have the FORMAT() function applied to them?

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.

Amit_Saxena wrote: 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"), Yes it does. You can just type in your custom format string. Both measures have the same expression in the example below, I just used a custom format string on the second one.

In case you have created a measure like Month year = format(Date[date],"MMM-YYYY") which is text and not take date sorting anymore create a measure like Month year Sort= format(Date[date],"YYYYMM") and mark it as sort column

This is not actually a bug. When you call the FORMAT function you are converting the number to a string. When you sort values like 1,2,4,22 as strings It will sort as "1","2","22","4" with all the "numbers" that start with the same digit grouped together. If you can, you need to apply the formatting by setting the format property on the measure. However this will only work if you have a static format, if you are trying to dynamically change the format based on some sort of logical expression then you should vote for the idea on ideas.powerbi.com to add support for calculation groups to Power BI desktop as this functionality would let the engine apply formatting dynamically without using the FORMAT function.

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.

Sort with DAX Function FORMAT | Microsoft Fabric Community

12 Replies

amitchandak
Super User
6 years ago
In case you have created a measure like

Month year = format(Date[date],"MMM-YYYY") which is text and not take date sorting anymore

create a measure like

Month year Sort= format(Date[date],"YYYYMM") and mark it as sort column
d_gosbell
Super User
6 years ago
This is not actually a bug. When you call the FORMAT function you are converting the number to a string.

When you sort values like 1,2,4,22 as strings

It will sort as "1","2","22","4" with all the "numbers" that start with the same digit grouped together.

If you can, you need to apply the formatting by setting the format property on the measure.

However this will only work if you have a static format, if you are trying to dynamically change the format based on some sort of logical expression then you should vote for the idea on ideas.powerbi.com to add support for calculation groups to Power BI desktop as this functionality would let the engine apply formatting dynamically without using the FORMAT function.
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
  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
    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

Forum Discussion

Sort with DAX Function FORMAT

12 Replies

Related content

Matrix - formatting and Sorting

Sorting

Table Sort , sort order , comparer

sorting matrix

User Defined Function - Dynamic Formatting