Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Aug 15, 2021
Solved

DAX IF error: the syntax is incorrect

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 "Wh...
BI & Data Analysis
power bi
  • bartvana's avatar
    bartvana
    Aug 15, 2021

    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.)

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 15, 2021

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.

bartvana's avatar
bartvana
Iron Contributor
Aug 15, 2021
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...
  • bartvana's avatar
    bartvana
    Iron Contributor
    Aug 15, 2021

    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.)

     

Resources