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

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

 

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.