Sort with DAX Function FORMAT

[Roberto456]

Hello All.

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

[Greg_Deckler]

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.

[Roberto456]

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"))

[v-shex-msft]

Hi @Roberto456,

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

[Amit_Saxena]

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]

---

@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]

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

[d_gosbell]

---

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

[Amit_Saxena]

Super! it works!! Thanks alot!!!

[d_gosbell]

---

@v-shex-msft wrote:

Hi @Roberto456,

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.

---

@v-shex-msft If you read the response from @Roberto456  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.

[v-shex-msft]

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)

@Roberto456 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

[d_gosbell]

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.

[amitchandak]

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