Issue with Decimal Separator in DAX Formulas ( function SQRT)

[Martin_2]

Hello,

I'm experiencing an issue with my DAX formulas in Power BI due to the decimal separator used in my locale.

In my locale, we use a comma (,) as the decimal separator. However, it seems like this is causing issues when I try to perform calculations in DAX.

Here's an example of a formula that's causing problems:

Safety_Stock = Pivot_Result[Coefficient service] *
SQRT(
(Pivot_Result[Average Lead time (Month)] * POWER(Pivot_Result[StdDeviation], 2)) +
POWER((Pivot_Result[Average / Month] * Pivot_Result[Average Lead time (Month)]), 2)
)

When I run this formula, I get an error message saying "An argument of function 'SQRT' has the wrong data type or the result is too large or too small."

I've checked and all my inputs are numeric fields and none of them are negative. Also, the results of these calculations are within a reasonable range and should not be too large or too small for the SQRT function to handle.

I suspect that this issue is due to how Power BI is interpreting decimal numbers in my locale. When I replace commas with periods in my numeric values manually, the formula works fine.

However, when I try to automate this process using SUBSTITUTE and VALUE functions in DAX, it doesn't work because VALUE seems to convert text back into numbers using a period as the decimal separator regardless of locale settings.

Is there any way to handle this issue directly within Power BI without having to change my system's regional settings or manually replacing commas with periods in my source data? Any help would be greatly appreciated.

Thank you.

Martin

[Anonymous]

Hi @Martin_2 ,

Please try to replace the commas with periods in Power Query, then load into Power BI.

In Power Query, you could do replace operation:

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.

[amitchandak · Accepted solution]

@Martin_2 , First of all check what value is this giving

(Pivot_Result[Average Lead time (Month)] * POWER(Pivot_Result[StdDeviation], 2)) +
POWER((Pivot_Result[Average / Month] * Pivot_Result[Average Lead time (Month)]), 2)

and is that a number and falls in the range of SRT

if this part gives an error. means one of the value is not having correct data type

[Martin_2]

Hello amitchandak / everyone,

I would like to apologize for any confusion caused by my previous post regarding an issue with decimal separators in DAX formulas. Upon further investigation, I discovered that there were indeed negative values in my data set which were causing the "SQRT" function to fail.

In my initial post, I had mistakenly assumed that all my inputs were positive numbers. However, after a more thorough review of my data, I found some negative values which were causing issues with the square root calculation.

Here is the updated formula that handles negative values correctly:

Safety_Stock = Pivot_Result[Coefficient service] *
SQRT(
IF(
((Pivot_Result[Average Lead time (Month)] * POWER(Pivot_Result[StdDeviation], 2)) +
(Pivot_Result[Average / Month] * POWER(Pivot_Result[Average Lead time (Month)], 2))) < 0,
0,
((Pivot_Result[Average Lead time (Month)] * POWER(Pivot_Result[StdDeviation], 2)) +
(Pivot_Result[Average / Month] * POWER(Pivot_Result[Average Lead time (Month)], 2)))
)
)

This formula first calculates the expression inside the SQRT function and checks if it's less than zero. If it is, we replace it with zero; otherwise, we use the calculated value.

This ensures that we're not trying to take the square root of a negative number.

I hope this update clarifies the situation and I apologize for any misunderstanding.

Thank you all for your patience and assistance, 🙂

Martin