DAX SWITCH syntax error . no matter what I do

[Anonymous]

Hi, I can't get DAX SWITCH to work no matter what I do. I have tried with the most simple example of just replacing a column of no. 1,2,3,4 with Q1,Q2,Q3,Q4 and I still get syntax error.

I presently have one measure which calculates an amount as a sum and another one, which calculates a running total. The first screenshot shows the measure for the running amount.

I then would like to exchange the column called HeaderCalcType with either the sum measure amount or the running amount. 

This is my input:

=SWITCH(

[HeaderCalcType]

,

BLANK(), BLANK()

,

1, [AmountDKK]

,

2, [RunningAmount]

)

And this is the error I get: 

If someone could help me solve this I would greatly appreciate it. I have made a lot of searches already trying to troubleshoot, but with no succes.

[Anonymous · Accepted solution]

So my brilliant husband figured out what the problem was and it did actually have to do with the separators not being read correctly. Apparently there is a bug in the system, so because my PC regional settings are set to English (Denmark), it causes single commas to sometimes be converted to periods. My husband found the workaround in a post titled "My commas are being seen as periods in a DAX formula!" and the solution implies that you have to use a double comma in the places where the error message indicates that you put a period. Like in this example:

[123abc]

The error you're encountering in your DAX SWITCH statement may be due to a few potential issues. Let's troubleshoot:

1. Data Types: Ensure that the data types of [HeaderCalcType], [AmountDKK], and [RunningAmount] are compatible. [HeaderCalcType] should likely be a numeric type (integer or similar), while [AmountDKK] and [RunningAmount] should be numeric types as well (like decimal or integer).

2. Column Existence: Double-check that [HeaderCalcType], [AmountDKK], and [RunningAmount] are indeed the names of columns in your dataset or measures that have been defined in your model. Sometimes, misspellings or incorrect column names can lead to syntax errors.

3. Context: Ensure that the measure containing the SWITCH statement is in the appropriate context. Depending on where you're using it (e.g., in a table visual, as a calculated column, in a Power BI measure), the context might affect how the SWITCH statement is evaluated.

4. Parentheses: While not necessary in all cases, wrapping the entire SWITCH statement in parentheses can sometimes help clarify the expression and ensure proper evaluation.

Here's a revised version of your SWITCH statement with parentheses:

= SWITCH (
[HeaderCalcType],
BLANK(), BLANK(),
1, [AmountDKK],
2, [RunningAmount]
)

If you've checked the above points and the error persists, you might want to try simplifying the SWITCH statement to isolate the problem. For instance, you could try using just one condition and its corresponding result to see if that works. If it does, gradually add more conditions until you identify where the issue lies.

If you continue to experience difficulties, please provide more context or additional details, and I'd be happy to assist you further!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Hi 123abc,

thanks a lot for your swift response and your helpful suggestions.

I have double checked  the data types and the two amounts are decimal numbers, while the HeaderCalcType is a whole number. All the measures are sitting in the same table in the PowerPivot data model and I'm confident that the spelling is correct.

I have also tried pasting your solution in and I get the exact same error message (I also had the parenthesis in my original one).

Your suggestion with trying something more simple is not going well either. Here is a table with no. 1-4, which I am trying to replace with quarters: 

[Anonymous]

It's almost like it's not reading some of the list separators correctly, but I have also tried changing them to ";", which doesn't work either. And when I calculate my running totals, I use "," as a separator and it works just fine.