Filter View with Multiple True/False Columns

Hi all,

I am trying to filter a view “LETTERS” that has four boolean dimensions “A”, “B”, “C”, and “D”. What I am trying to do is add a dimension filter called “SERVICE” and have four options to choose from “A”, “B”, “C”, and “D”. When “SERVICE” is filtered to “A”, then the “A” dimension is filtered for TRUE values. Please see below what I mean (in a table format).

SERVICE dimension
image

LETTERS view
image

SERVICE dimension set to “A”
image

LETTERS view filtered based on SERVICE
image

Many thanks,
Jason

Hi Jason! Welcome to the community! Absolutely you can do this with Looker. First of all, do you have your database structured in the image 2? If so, you can do a pivot table to structure this on Looker looks. Thereafter, just add a filter on column A/B/C/D to filter for “TRUE”. :slight_smile:

Here’s an example! Pivot the category and insert your ID and T/F column.

Thanks Nicholas. Is there a way to achieve this without pivoting?

Hey Jason, I think this may be possible to be done without pivoting by using a CASE WHEN dimension.

dimension: service_filter {
    type: string
    sql: CASE WHEN ${letters.dimension_a} THEN "A"
              WHEN ${letters.dimension_b} THEN "B" 
              WHEN ${letters.dimension_c} THEN "C"
              WHEN ${letters.dimension_d} THEN "D"
         ELSE NULL END ;;
    }

This assumes the fields are inherently true or false. So for example, if we try to filter the new field Service Filter for the value “C” then our results will only display rows where ${letters.dimension_c} is TRUE.

1 Like