Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
naoram
New Member

DAX SWITCH syntax error . no matter what I do

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.running amount.PNG

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

 

switch.PNG

This is my input:

 

=SWITCH(

[HeaderCalcType]

,

BLANK(), BLANK()

,

1, [AmountDKK]

,

2, [RunningAmount]

)

 

And this is the error I get: 

error.PNG

 

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.

1 ACCEPTED SOLUTION
naoram
New Member

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:

switch corrected.PNG

View solution in original post

4 REPLIES 4
naoram
New Member

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:

switch corrected.PNG

123abc
Community Champion
Community Champion

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.

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: quarters.PNG

 

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors