Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Extra closing parenthesis added to a DAX Switch statement

I need to do conditional formatting based on two fields instead of one.  To do this, I created a switch statement that creates an integer state variable that (I hope) can be used in conditional formatting. 

 

There are four basic states that each have a different background color:

1. Extreme

2. Loud

3. Moderate

4. Quiet

 

For each of the four states, I need to set the font color to hide 0 entries or display entries that are higher than 0. (So 4 times 2 = 8 states).

 

However, when I hit Enter to complete my DAX statement based on a SWITCH expression, additional closing parameters are automatically added to the end.  The additional closing parenthesis make the DAX statement invalid.

 

The DAX Statement that I am trying to create is below.  

 

12amState = SWITCH(MAX([NoiseCategory],0),
                   1, IF(MAX([12am],0)=0, 1, 2),
                   2, IF(MAX([12am],0)=0, 3, 4),
                   3, IF(MAX([12am],0)=0, 5, 6),
                      IF(MAX([12am],0)=0,7,8))

 

 

When I hit Enter to complete the DAX Statement, additional parenthesis are added and the expression is flagged with a syntax error.

 

Need Help

3 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous ,

    As far as I can see, you are creating a calculated column.. I don't see any issues:

    12amState = 
SWITCH (
    MAX ( [NoiseCategory], 0 ),
    1, IF ( MAX ( [12am], 0 ) = 0, 1, 2 ),
    2, IF ( MAX ( [12am], 0 ) = 0, 3, 4 ),
    3, IF ( MAX ( [12am], 0 ) = 0, 5, 6 ),
    IF ( MAX ( [12am], 0 ) = 0, 7, 8 )
)

    You can check your formula here for syntax errors and for better formatting: https://www.daxformatter.com/

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

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Does it help if you use a variable?

     

    12amState =
VAR Max0 = ( MAX ( [12am], 0 ) = 0 )
RETURN
    SWITCH (
        MAX ( [NoiseCategory], 0 ),
        1, IF ( Max0, 1, 2 ),
        2, IF ( Max0, 3, 4 ),
        3, IF ( Max0, 5, 6 ),
        IF ( Max0, 7, 8 )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please check the example I tested. It works fine and it is a calculated column.

    You added 0 to the MAX function. This is to compare [12am] with 0 and then take the maximum value.

     

    If you want to create a measure, you cannot use MAX([12am],0).

     

    Can you provide some sample data and expected results? In this way, I can help you modify the formula according to the logic of your expected result.

     

    Best Regards,

    Stephen Tao

     

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