Search code examples
looker-studio

How can I filter for specific combinations in Google Data Studio?

I have a data set with multiple lines per Person and the Fruit Selection on a particular date. I would like to create a filter to show me people that selected specific combinations (i.e. apple and pear). Ideally I would like to be able to incorporate a date filter as well but this is a secondary need.

I tried using the following as a new field, as a metric, and as a calculated control field but I get the same error message for all of them:

CASE
  WHEN SUM(Apple) > 0 AND SUM(Orange) > 0 AND SUM(Pear) = 0 THEN 'Apple and Orange'
  WHEN SUM(Apple) > 0 AND SUM(Orange) = 0 AND SUM(Pear) = 0 THEN 'Apple'
  WHEN SUM(Apple) = 0 AND SUM(Orange) = 0 AND SUM(Pear) > 0 THEN 'Pear'
  WHEN SUM(Apple) = 0 AND SUM(Orange) > 0 AND SUM(Pear) = 0 THEN 'Orange'
  WHEN SUM(Apple) > 0 AND SUM(Orange) = 0 AND SUM(Pear) > 0 THEN 'Apple and Pear'
  WHEN SUM(Apple) > 0 AND SUM(Orange) > 0 AND SUM(Pear) > 0 THEN 'Orange and Pear'
  ELSE 'other'
END

I keep receiving this error message:

Metric expressions and aggregations are not allowed in this expression.

however I don't have any metrics included in the calculation.

Resources that might help:

Data layout (Google Sheets):

Person date Fruit Selection Apple Orange Pear
Person A 1/1/2019 Apple 1 0 0
Person A 2/1/2019 Orange 0 1 0
Person B 2/2/2019 Pear 0 0 1
Person B 2/3/2020 Pear 0 0 1
Person B 2/4/2020 Apple 1 0 0
Person C 2/5/2021 Apple 1 0 0
Person C 2/6/2019 Orange 0 1 1
Person C 2/7/2020 Pear 0 0 1
Person C 2/8/2019 Pear 0 0 1

Google Data Studio report

Solution

  • Custom values can be used in a the control, by using incorporating a parameter, supported by calculated fields:

    1) Parameter

    Add a parameter (called Fruits here):

    • Data type: Text
    • Permitted values: List of values

    Then enter the following values (with All set as the default value, containing the entire unfiltered data set):

    • All
    • Apple
    • Orange
    • Pear
    • Apple and Orange
    • Apple and Pear
    • Orange and Pear

    fruits_parameter

    2) Calculated Fields

    Create the calculated fields below at the data source. Note that the first six calculated fields (beginning with 2.1) HIDE_Apple and ending with 2.6) HIDE_Orange and Pear) contain the word HIDE to indicate that they can be hidden (otherwise the fields list in the data source will be cluttered) as they are used as support fields in the CASE statement (2.7) Fruits_CASE).

    2.1) HIDE_Apple

    REGEXP_EXTRACT(Fruit Selection, "Apple")

    2.2) HIDE_Orange

    REGEXP_EXTRACT(Fruit Selection, "Orange")

    2.3) HIDE_Pear

    REGEXP_EXTRACT(Fruit Selection, "Pear")

    2.4) HIDE_Apple and Orange

    REGEXP_EXTRACT(Fruit Selection, "(Apple|Orange)")

    2.5) HIDE_Apple and Pear

    REGEXP_EXTRACT(Fruit Selection, "(Apple|Pear)")

    2.6) HIDE_Orange and Pear

    REGEXP_EXTRACT(Fruit Selection, "(Orange|Pear)")

    2.7) Fruits_CASE

    CASE Fruits
  WHEN "All" THEN Fruit Selection
  WHEN "Apple" THEN 2.1) HIDE_Apple
  WHEN "Orange" THEN 2.2) HIDE_Orange
  WHEN "Pear" THEN 2.3) HIDE_Pear
  WHEN "Apple and Orange" THEN 2.4) HIDE_Apple and Orange
  WHEN "Apple and Pear" THEN 2.5) HIDE_Apple and Pear
  WHEN "Orange and Pear" THEN 2.6) HIDE_Orange and Pear
  ELSE NULL
END

    Once that's done, the data source would look like:

    fields_data_source

    3) Controls, Charts & Filters

    3.1) Control

    control_fruits

    3.2) Pivot Table

    Create a pivot table:

    • Date range dimension: date
    • Row dimension: Person
    • Column dimension: 2.7) Fruits_CASE
    • Metric: Record Count

    pivot_table_fruits

    3.3) Filter

    NULL values created in the 2.7) Fruits_CASE field can be hidden by using the filter:

    Exclude 2.7) Fruits_CASE Is NULL

    filter_null

    Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

    gif