Search code examples
google-sheets-formulalooker-studio

using WHEN and CASE in Data Studio formulas

I'm used to writing in JavaScript so I am approaching this thinking about SWITCH statements and of course the classic if else...

I just want to check if the data in a row is TRUE, and if it is count it. If the value is not true, don't add it to the count.

I'm thinking something like this would work:

CASE

WHEN is_it_true_or_false = false

THEN COUNT_DISTINCT ( id ) //using id to address that specific row and add it to the count

END
Solution

  • It can be achieved by using either of the following CASE statements and aggregating the Calculated Field as required - COUNT_DISTINCT (Unique IDs of TRUE values) or COUNT (All TRUE values):

    1) Where is_it_true_or_false is a Boolean OR String Field:

    CASE
  WHEN REGEXP_MATCH(is_it_true_or_false, "((?i)TRUE)") THEN id
  ELSE NULL
END

    2) Where is_it_true_or_false is a Boolean Field:

    CASE
  WHEN is_it_true_or_false = TRUE THEN id
  ELSE NULL
END

    Google Data Studio Report and GIF to elaborate: