Help understanding logical conditions in CALCULATE

[y_s_c]

Hi all,

So, I can't get my head around this one (and I'm sure the explanation would be rather easy).

I have a Likert scale that includes two response categories I want to exclude. These categories are "Did not answer" and "Not applicable." To calculate the count of positive responses I'd do:

Positive satisfaction = 
CALCULATE (
    COUNT ( Table[Response ID] ),
    Table[My scale] = "Very Satisfied"
        || Table[My scale] = "Satisfied"
)

Then, if I wanted to get some percentages ignoring "Did not answer" and "Not applicable" I know I could do something like:

Positive satisfaction % = 
DIVIDE (
    [Positive satisfaction],
    CALCULATE (
        COUNT ( Table[Response ID] ),
        ALL (
            Table[My scale]
        ),
        Table[My scale] <> "Did not answer"
            && Table[My scale] <> "Not applicable"
    )
)

When I re-read my code above I instinctively think that the filtering conditioning being used demands that [Response ID] equals both "Did not answer" and "Not applicable" at the same time. What would be the correct interpretation of CALCULATE here? Any and all help very much appreciated. Cheers!

[lbendlin · Accepted solution]

Not sure what your issue is here but if you want you can rewrite the code as 

Positive satisfaction % = 
DIVIDE (
    [Positive satisfaction],
    CALCULATE (
        COUNT ( Table[Response ID] ),
        ALL (
            Table[My scale]
        ),
        Table[My scale] NOT IN {"Did not answer","Not applicable"}
    ),
    0
)

[y_s_c]

Just for clarity in case someone finds the above in the future, NOT IN should be instead employed as:

Positive satisfaction % = 
DIVIDE (
    [Positive satisfaction],
    CALCULATE (
        COUNT ( Table[Response ID] ),
        ALL (
            Table[My scale]
        ),
        NOT ( Table[My scale] ) IN {"Did not answer","Not applicable"}
    )
)

[y_s_c]

Thanks for your reply. Creating the measure isn't my issue per se. It was more about understanding more deeply what's going on when filtering inside CALCULATE.

I'm more used to other languages and when I see something like the below I instinctively think the filtering works as "something is TRUE, something else is TRUE (at the same time)." Logically, a response can't be "Did not answer" and "Not applicable" at the same time, thus my confusion.

Table[My scale] <> "Did not answer"
            && Table[My scale] <> "Not applicable"

[lbendlin]

DAX is no different from other languages in this area.  What trips people up is the difference between logic statements and everyday language.

When you say " I want the red shirts and the blue shirts " you can't write it as Shirts="Red" && Shirts="Blue".  The logic equivalent is Shirts="Red" || Shirts = "Blue",  or Shirts IN {"Red","Blue"}

[y_s_c]

I do understand what you're saying there, and whilst I can write that measure in DAX as originally posted, I still need to pause and think why on earth I need "&&" and not "||".

In R I would think in terms of:

filter ( myscale != "A" | myscale != "B")

 
But that's not the case inside CALCULATE, is it? Second code chunk in my original post, if changing "&&" to "||" the DENOMINATOR ends up including both categories I want to exclude.

Sorry for keeping having a go at this, but I feel I'm missing something really obvious 😞

[lbendlin]

filter ( myscale != "A" | myscale != "B")

Careful! this may not do what you think it should do.  It evaluates to TRUE as all of your items will satisfy either condition.