Aug 15 2021 03:00 AM
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.
Aug 15 2021 03:25 AM - edited Aug 15 2021 03:26 AM
@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.
Aug 15 2021 03:30 AM
Aug 15 2021 03:33 AM
@bartvana Did you try with semi-colons?
Aug 15 2021 04:02 AM
@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.
Aug 15 2021 04:08 AM
Aug 15 2021 04:18 AM
@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.
Aug 15 2021 04:39 AM
Aug 15 2021 05:20 AM
SolutionI 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.)
Aug 15 2021 05:20 AM
SolutionI 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.)