My DAX in Excel powerpivot does not understand numbers

[Yuriy-Agri]

When I put this minus 1 or any other number the formula does not regard it as a number. 
Only one way to force it t work is to put {}, then everything work.

But it is not how it should be. 
In Power BI everything works as shall be without any {}
What is wrong with my Excell 365 ?

[Yuriy-Agri]

No, it is not working. 
It looks like DAX formulas in my Excel 365 understand numbers/dot/coma incorrectly. I made another test workbook.

And if I put the curve brackets around 5, then the formula works well.

And the same DAX formula works ok in Power BI

[OwenAuger · Accepted solution]

Hi @Yuriy-Agri 

I see that your regional settings have the comma as the decimal separator.

I was able to replicate the behaviour you're seeing by switching my Window Region/Format from my usual  "English (New Zealand)" to "English (Germany)" to enable the comma decimal separator.

It seems that with these regional settings, when authoring DAX expressions in the Power Pivot interface, commas can be treated either as decimal separators or list separators depending on the context.

1. When a comma immediately follows digits in such a way that it could be a valid decimal separator, it is interpreted as a decimal separator.
2. Otherwise, it is interpreted as a list separator.

Assuming you want to leave your Region/Format settings unchanged, it seems that for any expression that contains a number with no decimal places followed by a list separator, you must ensure some other character is inserted between the number and the list separator.

This character can simply be a space, an extra comma to act as a redundant decimal separator, or you could wrap the number in brackets or even a table constructor as you have already tried.

So all of these expressions should work:

Test_measure := IF ( SUM ( Test_table[Amount] ) > 5 , "true", "F" )

Test_measure := IF ( SUM ( Test_table[Amount] ) > 5,, "true", "F" )

Test_measure := IF ( SUM ( Test_table[Amount] ) > (5), "true", "F" )

Test_measure := IF ( SUM ( Test_table[Amount] ) > {5}, "true", "F" )

Do I think this is buggy? Yes, because I was under the impression that comma decimal separators went hand-in-hand with semicolon list separators, just as they do in Excel formulas.

I'll look into querying this with Microsoft and reporting as a bug, and you may want to as well. I'll post back if I learn anything else.

Regards

[v-xianjtan-msft]

Hi @Yuriy-Agri 

I have not encountered the same problem as you in my testing.
Please try checking the following to see if it resolves it.
1. Make sure the DAX formula has no syntax errors and references the column names correctly.
2. Try updating Excel to the latest version.
If the problem persists, please go to the Excel forum Welcome to the Excel Community - Microsoft Community Hub for professional support.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xianjtan-msft]

Hi @Yuriy-Agri 

Just a friendly follow up on the case. May I know if the problem has been resolved?

If you have any Power BI related problems, please feel free to contact me.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.