IF function doesn't work

[Katerina]

Hi all!

I meet a strange error in my PBI file. This file was created last year and all worked perfect. Today I opened it and see the error in following function:

11 = var q = 0,7 RETURN IF( 'table'[Column] > q, "1", "2" )

The error is: "The following system error occurred:". Without any details.

I'm using the latest version of Power BI Desktop. Regional settings - English. Windows language - Engling.

Can anybody help me please?

Thanks in advance!

[amitchandak]

Seems like . or , or ; issue

Try like

11 = var q = 0.7
RETURN IF( 'table'[Column] > q, "1", "2" )

OR

11 = var q = 0,7
RETURN IF( 'table'[Column] > q; "1"; "2" )

[Katerina]

@amitchandak Hi!

I've tried this, but it didn't help 😞

I had simmilar error in my Calendar function and resolve this issue by adding spaces before coma in the formula:

Calendar= CALENDAR(DATE(2012 ,1 ,1),DATE(2019 ,12 ,31))

Without extra spaces this formula didn't work.

But I can't resolve the issue with IF formule 😞

[amitchandak]

This was

11 = var q = 0,7
RETURN IF( 'table'[Column] > q, "1", "2" )

have tried like

11 = var q = 0.7
RETURN IF( 'table'[Column] > q, "1", "2" )

[Katerina]

My Power BI Desktop does'n accept 0.7

If I put with dot - PBI change it to comma. If I again put dot - PBI mark it with red.

I use comma in PBI. For an example in the Column with decimal I see 6,0 etc.

[Katerina]

very strange, but this formula is work:

11 = 

var q = VALUE("0,7")

RETURN

IF('Продукты'[Заполненность склада]>q ,"1" ,"2")

But it is not a good idea to add Value function allways when I need to work with Decimal 😞

[amitchandak]

Under file - >Option and Setting  -> Option -> Regional setting

Check that. You can not have, at both place

[Katerina]

Application language and Model language - English (United States)

[Stachu]

I think @amitchandak is right, it's because of regional settings. My guess is that you don't have standard list and decimal separator, and they are both set to "," (I strongly recommend changing it, especially if you work with csv files), similar to the post here:
https://community.powerbi.com/t5/Power-Query/IF-with-text-and-numbers/m-p/763520/highlight/true#M255...

If that's the case this code may work (added space after q)

11 = 
var q = 0,7 
RETURN IF( 'table'[Column] > q , "1", "2" )

as well as this one if you need numbers and not text - with the same decimal and list separator this is actually valid DAX, but you can see it's very confusing (1,0 is actually considered 1 in this case)

11 = 
var q = 0,7 
RETURN IF( 'table'[Column] > q , 1,0 , 2,0 )

[Katerina]

Ahh! Yes. I Found. In my Windows settings the decimal and list separators was "," - for both.

I changed decimal separator to "." and now all is work!

Thank you very much!

[Stachu]

Glad to hear it worked - can you please mark the topic as solved?