topic Re: Help understanding logical conditions in CALCULATE in DAX Commands and Tips

Help understanding logical conditions in CALCULATE

y_s_c — Fri, 15 Mar 2024 10:41:34 GMT

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!

Re: Help understanding logical conditions in CALCULATE

lbendlin — Fri, 15 Mar 2024 15:32:33 GMT

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 )

Re: Help understanding logical conditions in CALCULATE

y_s_c — Fri, 15 Mar 2024 15:48:44 GMT

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"

Re: Help understanding logical conditions in CALCULATE

lbendlin — Fri, 15 Mar 2024 15:55:47 GMT

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"}

Re: Help understanding logical conditions in CALCULATE

y_s_c — Fri, 15 Mar 2024 16:11:36 GMT

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 😞

Re: Help understanding logical conditions in CALCULATE

lbendlin — Fri, 15 Mar 2024 16:16:11 GMT

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.

Re: Help understanding logical conditions in CALCULATE

y_s_c — Mon, 18 Mar 2024 09:59:23 GMT

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