looker-studio

Using Array Field for Filtering Rows


I have a field which contains an array of Tags. I would like to be able to filter my table based on whether each row contains that tag. However Looker seems to be treating the entire array as an entity rather than allowing me to filter individually by each tag.

Screenshot showing filter control

For example: I want to be able to filter to show all rows that contain "Blog" for example, not which equal "Article, Blog, Image".


Solution

  • This is doable, but not as straightforward as you would expect. This unnecessarily complicated approach is required because in Looker Studio a filter cannot use a parameter value directly. So:

    I have created a test table looking like this:

    id type spent
    1 ["Article", "Blog", "Image"] 12.34
    2 ["Image" "Blog"] 24.43

    First, and this is the major downside, you need to manually create a parameter for your data source ("Add a parameter"). Let's call it "param_types". It contains all the different values for project type, like this:

    enter image description here

    Then you create a Drop-Down List and set the control field to your newly created parameter "param_types".

    Then you create a Custom Variable (let's call it "type_contains_param") that returns True/False if the selected dropdown item (param_types) is contained in the type array, like this:

    CASE
        WHEN CONTAINS_TEXT(type, param_types) THEN TRUE
        ELSE FALSE
    END
    

    This custom variable you can use as a filter for your table. Create a new filter defined as

    Include / type_contains_param / True
    

    and apply it to your table.

    Make sure that your table's "Paramters" (SET-UP pane) is set to "Default (<>)", otherwise this won't work. When you select Default it should display as "param_types".

    enter image description here

    VoilĂ .