PowerPivot - Syntax error message because of separator (";" or ",") Getting error either from "Formula checker" or from MdxScript

[PierreBenko]

Hi!

I am using Excel in Germany, with German locale settings.
I have met with a very odd behaviour from Excel:
When I write the formula for calculated columns I use ";" as separator (as for any other Excel formula) between the arguments. The Formulae are working fine, everything is tip-top.
But when I am creating measures in PowerPivot, I have to use "," as separator. Most of the time.
The problem is, that the DAX Formula checker throws an error for some formulae (not for all), when I use "," as separator. If I change the separator to ";" , it passes the check from the formula checker, but then the MdxScript throws an error saying: "We couldn't get data from the Data Model. Here's the error message we got: MdxScript(Model) (6, 68) The syntax for ';' is incorrect."

This means, that I can use CALCULATE with FILTER if I use "," as separator, but e.g. MIN or IF throws an error in both cases, no matter which separator I use.

Has anyone had this problem before? Is there any workaround?

Thank you very much for any help in advance!

Here are a few screenshots:
MIN with semicolon, everything is fine

After clicking on "OK":

The same formula with comma:

 

[GraH]

Hello, the separator is not the issue. The formula is. Not sure what you are after, but min([UN_Wachstum_Raw]) returns the min of that column in the context the measure is used.
Min(0; Min(Un_Watchtstum_Raw) might work. As will =Min(0;MINX(Table;[UN_Wachstum_Raw])). I hold a bit of reserve since I can't see you data.

 

[PierreBenko]

Hello, the separator is not the issue. The formula is. Not sure what you are after, but min([UN_Wachstum_Raw]) returns the min of that column in the context the measure is used.
Min(0; Min(Un_Watchtstum_Raw) might work. As will =Min(0;MINX(Table;[UN_Wachstum_Raw])). I hold a bit of reserve since I can't see you data.

View attachment 20902

Hi GraH,

thank you for your answer.
The formula in my post was basically just an example. Nevertheless, I corrected it, which led to the exact same problem described in my post. The error message from MdxScript states it clearly, that the problem is with the semicolon, and that it is a syntax error.
I have a hunch, that the problem has something to do with invoking the Formula Engine and/or the Storage Engine, but I'm not that deep into the inner workings of DAX to be able to verify it.

Best regards,
Peter

 

[GraH]

Hi Peter,
I'm rather sure the ";" error is about the ";" turning up in a place where it does not belong rather than it being the wrong separator. The expression is invalid.
From the DAX online mso help, a correct application of the function using 2 arguments would be like:
= Min([TotalSales], [TotalPurchases]). You cannot simply use a number and a whole column and return the lowest value.

I whish I could offer a solution, but without the data at hand that's difficult.