Extra closing parenthesis added to a DAX Switch statement

[MDKCLIRResearch]

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.

[Anonymous]

Hi @MDKCLIRResearch ,

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.

[AlexisOlson]

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 )
    )

[ERD]

@MDKCLIRResearch ,

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.