Forum Discussion

cuohanele's avatar
cuohanele
Icon for Helper I rankHelper 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
artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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?

  • cuohanele's avatar
    cuohanele
    Icon for Helper I rankHelper 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
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      oh, this is a DAX question.

       

      Just change the data type of the measure to number:

       

      • cuohanele's avatar
        cuohanele
        Icon for Helper I rankHelper 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