Forum Discussion

cuohanele's avatar
cuohanele
Helper I
6 years ago

CONVERT TEXT TO NUMBER

Is there a way to change a text datatype to a number? Having issues with that. Maybe a way to bypass the error message that a text can't be converted to a number. I've tried advanced editor. Please h...
Need Help
cuohanele's avatar
cuohanele
Helper I
6 years ago

I created a dynamic format for a column in my table to be filtered by a specific slicer. Unfortunately, it is sorting the numbers like they are string. I used the code below:

 

Sales$ = IF(ISCROSSFILTERED('Parameter'[calculations]),
SWITCH( TRUE(),
    VALUES('Parameter'[calculations]) = "YoY%",
SWITCH( TRUE(),
ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%],"0.0%"),
ABS([Sales$ YoY%]) >= 1, FORMAT([Sales$ YoY%],"0%"),
BLANK()
),
    VALUES('Parameter'[calculations]) = "YoY Gap", CONCATENATE("$", FORMAT([Sales$ YoY Gap],"0,0")),
    VALUES('Parameter'[calculations]) = "Current Year Actuals", CONCATENATE("$", FORMAT([Sales$ CY],"0,0")),
BLANK()), BLANK ())
 
When any of the options in the slicer is picked, it returns this column sorted out of order. 
So it is sorting something like this:
100
1
2
200
222
3
33
 
It's formating as a text but I want it to format as a number
Thanks in advance!
artemus's avatar
artemus
Microsoft Employee
6 years ago

oh, this is a DAX question.

 

Just change the data type of the measure to number:

 

  • cuohanele's avatar
    cuohanele
    Helper I
    6 years ago

    That's the issue. It is greyed out. Used DAX ro crossfilter column to the slicers but the datatypes are different for all three dropdown options. One is percent and the others are currency. I can't switch them all to the same datatype

     

    • artemus's avatar
      artemus
      Microsoft Employee
      6 years ago

      You can add a new column (which doesn't depend on that one) which is numbers and can be set as the sort order for you main column

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi cuohanele 

      Create a dax column to convert text column to number:

       

      text to number = CONVERT([text column],INTEGER)

       

      Then sort by this column to get proper order.

       

      Or create another column besides your [Sales$] column,

      then display [Sales$] column on your visual, but sort by the following column

      Sales$ 2 =
IF (
    ISCROSSFILTERED ( 'Parameter'[calculations] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Parameter'[calculations] ) = "YoY%", SWITCH (
            TRUE (),
            ABS ( [Sales$ YoY%] ) < 1, FORMAT ( [Sales$ YoY%], "Fixed" ),
            ABS ( [Sales$ YoY%] ) >= 1, FORMAT ( [Sales$ YoY%], "Fixed" ),
            BLANK ()
        ),
        VALUES ( 'Parameter'[calculations] ) = "YoY Gap", CONCATENATE ( "$", FORMAT ( [Sales$ YoY Gap], "Fixed" ) ),
        VALUES ( 'Parameter'[calculations] ) = "Current Year Actuals", CONCATENATE ( "$", FORMAT ( [Sales$ CY], "Fixed" ) ),
        BLANK ()
    ),
    BLANK ()
)

       

       

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.