Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
NPbon
New Member

Force Convertion of String to Number using Dax

Hello everyone,

 

I have a column that was created with DAX (hence cannot use power query) as a Text that needs to be converted to Number.

However, sometimes on this column there are non-numerical, non-roman characters as the source is taken from multiple languages around the world. 

When using CONVERT function, if only in one of the rows there is a special character, all rows are changed to  #Error.

 

I want to know if there is a function that forces the convertion (omitting or deleting the special character) or at least that changes to #Error only the row with the problem, and not the entire column. 

 

Many thanks,

2 REPLIES 2
NPbon
New Member

Hello,
The solution provided Did not work for the intended purpose at all.

When using it I got an error: "Expressions that yield variant data-type cannot be used to define calculated columns".

An Example of my problem, and how I would like the solution (using 0.04 as error code)
14.45  --> 14.45
14#  --> 0.04
103 --> 103
$18 --> 0.04

when trying to use 0.04 instead of "Not number", all rows became 0.04

Any other idea with this?



Anonymous
Not applicable

Hi @NPbon ,

Plese try to identify the column firstly, then convert the data type, please try to create a column like below dax formula:

 

Column =
VAR cur_val = [Column Name]
VAR _a =
    SWITCH (
        TRUE (),
        ISNUMBER ( cur_val ), CONVERT ( cur_val, INTEGER ),
        ISNONTEXT ( cur_val ), CONVERT ( cur_val, INTEGER ),
        "Not number"
    )
RETURN
    _a

 

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.