cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 help. Thanks!

10 REPLIES 10
Highlighted
Microsoft
Microsoft

Can you provide a sample format of the numbers you are trying to parse along with the country code you are running power bi in?

Highlighted

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!
Highlighted

oh, this is a DAX question.

 

Just change the data type of the measure to number:

artemus_0-1596727957733.png

 

Highlighted

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

 

Highlighted

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

Highlighted

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.

Highlighted
Resolver III
Resolver III

Hi @cuohanele ,

 

Can you just share me the sample dataset and the .pbix file so ,that i can figure out the problem and provide you the solution.

 

Thank You

Highlighted
Memorable Member
Memorable Member

Try padding the "numbers" with trailing blanks so that all text ha same length.

Think like you would do some sort of right alignment

Ps

If you search the forum can find a post of mine with the code to do that

Highlighted
Memorable Member
Memorable Member

image.png

 

 

image.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors