Sort order problem: DAX formatting convert numbers to strings

[Anonymous]

Hi all! 

I am applying a format in a DAX-expressions. To be more exact, i do several DAX's in a switch statement. See below.

If i sort by these formatted values they are treated as text string, such that the sort order doesn't make sense!

I read somewhere, that this is a known issue and should have been fixed in the jun-2017 release. But i can't get it working.

Could anyone give some input? 🙂

Here's my code:

Measure1 =

CALCULATE(SWITCH( TRUE(),

VALUES( 'Metric Selection'[Metric1] ) = "Impressions",format([Impressions],"#,0"),

VALUES( 'Metric Selection'[Metric1] ) = "Clicks", format([Clicks],"#,0"),

...

...

,0),ALL('Measure3date'[measure3date]))

[v-yuta-msft]

Hi T_Fischer ,

You may try perdefined format string like below:

FORMAT( 12345.67, "General Number")  
FORMAT( 12345.67, "Currency")  
FORMAT( 12345.67, "Fixed")  
FORMAT( 12345.67, "Standard")  
FORMAT( 12345.67, "Percent")  
FORMAT( 12345.67, "Scientific")  

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Thanks a lot for your input.

It looked very promising but unfortunately it still converts numbers to a text string.

Br, Torben

[v-yuta-msft · Accepted solution]

Hi T_Fischer,

Click Modeling-> change format to Percentage.

 

Regards,

Jimmy Tao