DAX IF error: the syntax is incorrect

[bartvana]

The answer to this must be really simple but I can't find it...

I have the following formula in Power Pivot,

=IF([Minstock]>0, 1, 0)

where MinStock is a column in the same table of data type "Whole number",

but I get the error "The syntax for '1.' is incorrect", and I can't figure out why?

Could this have something to do with regional settings? In Excel formulas, I use semicolon instead of comma, and in my PowerPivot, the decimal numbers have a comma instead of a point.

[#2]

@bartvana See if this simple model works on your end. I'm using the comma as the list separator, but have read that local settings may influence DAX formulas. Not my expertise, however.

[#3]

@Riny_van_Eekelen It gives the same error when I select the formula and press Enter in Power Pivot:

[#4]

@bartvana Did you try with semi-colons?

[#5]

Yes, same error:

[#6]

@bartvana what if you change the local setting of your system to English (UK) and restart the lot? Otherwise I can't think of anything else.

[#7]

That's a bit drastic and would have other unwanted effects, I think? It can't be that the only way to use DAX is to have your while system set to English UK, right?

[#8]

@bartvana Google for "regional settings power pivot" or "regional settings DAX" and you'll see that regional settings can have an impact. Not for me, though, as I use English Excel with UK settings.

[#9]

Yes, it seems, though I don't find a solution to my problem anywhere. I don't want English separators and date formats everywhere in Excel, but I do want to use DAX formulas...

[#10 · Accepted solution]

I seemed to have solved it by changing the list separator in Windows Regional settings to semicolon, and then using the semicolon in the DAX formula.

=IF([MinStock] > 0; 1; 0)

For future reference, I found the settings here:

Windows key, then type "region":

In the "Regional Format" window, click on "Additional date, time & regional settings"

Then "Change date, time or number formats"

There: "Additional settings"

There, change "List separator" to semicolon:

I then restarted Excel, not sure if that was necessary.

(To be complete, I also first changed the decimal and grouping separators to respectively point (.) and comma (,) but finally reverted them and it still works.)