Forum Discussion
cuohanele
Helper I
Helper ICONVERT 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...
artemus
Microsoft Employee
Microsoft EmployeeCan you provide a sample format of the numbers you are trying to parse along with the country code you are running power bi in?
cuohaneleHelper I
Helper II 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
oh, this is a DAX question.
Just change the data type of the measure to number:
- cuohanele
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
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