[SOLVED] Major bug in DAX function for excel 365 home

[FredDF]

1. Add a measure
2. write

=SWITCH(TRUE,yearly_synthesis[perfs]<100,"A",yearly_synthesis[perfs]<= 100, "B","C")

"A" and "B" are underlined as error and the measure can not be saved returning: “The expression is not valid or appears to be incomplete.”

Lots of the logical functions are unusable with my excel version : Microsoft 365 MSO (Version 2308 Build 16.0.16731.20182) 64-bit :

IF, NOT, SWITCH, IFERROR, OR, AND

These :

=IFERROR(25/0,9999)

=OR(25=1,25=25)

=AND(10 > 9,-10 < -1)

returns: "Too few arguments were passed to the OR function. The minimum argument count for the function is 2."

All of these functions are very sensitive to spaces between arguments. They don’t allow space after the argument separator “,”

Even more major bug because it is the most used: the IF function is broken. The second argument refuses to accept function. Here is an example.

=IF(COUNTROWS(VALUES(tbl_february[id]))=1,VALUES(tbl_february[id]),"FALSE")

When you begin to write the second argument “values…” in the IF function, there is no autocomplete. If you force it to write manually, it detects it as an expression error and will refuse to save the measure. “The expression is not valid or appears to be incomplete”

I online repaired my office, I disabled/enabled power pivot addin to no avail.

I sent a feedback. I hope this will be fixed very soon. My project is stopped until then. If a Microsoft employee read this…

[#2 · Accepted solution]

by any chance is your computer set to a locality that uses 1.000,00 instead of 1,000.00? Have you tried using ; as the parameter separator?

[#3]

@mtarler I suspected that. Separator are not underlined with error unless I use ; instead of ,

My PC is set to use , as list separator.

But yes, I kept the french format for 1 000,00 instead of 1,000.00.

And I use dot in excel option.

After your message, I set the regional setting to English (us) and restart the PC then set excel to use system separators. It solved the issue. I’m gonna do more tests to see if I can only change the number format on my PC to fix this. It never was an issue until I use DAX. I wonder if I change my office language to french and reset the regional settings to french, if DAX would still complain. I prefer using office in English because I don’t like excel translate its function name. Thanks.

Edit : After further tests, if you set office in English, you can keep your French regional settings. But you need to change these. Decimal symbol as a dot (for currency too). List separator : comma.

Then use system separator

You need to restart your PC after those changes.