Search code examples
looker-studio

How can I filter related records in Google Data Studio from multiple data sources using a filter on a field only available in one of the data sources?

I have two data sources. Both data sources have a different schema but do share one ID field.

DS1

ID Name Age Country temp
1 anchal 23 US 7.666666667
2 nigam 10 CA 5
3 dingo 20 UK 3.333333333

DS2

DS1 ID Make Model
1 AAA BBB
1 CCC DDD
1 EEE FFF
3 GGG HHH
3 III JJJ

The DS1 ID field from DS2 is from DS1.ID.

I will have both of these as a table in a Google Data Studio report. Then I will add a filter on DS1.Country. This will obviously filter the DS1 table.

I want it to also filter on DS2 based on the matching DS2.DS1 ID rows against ID from DS1.

So, for example, if someone selects UK in the DS1.Country filter then this is what the report would show:

DS1.Country Filter

  • [ ] US
  • [ ] CA
  • [x] UK

DS1

ID Name Age Country temp
3 dingo 20 UK 3.333333333

DS2

DS1 ID Make Model
3 GGG HHH
3 III JJJ

I know how to filter two different data sources against the same field as discussed here but that is not what I am trying to do.

Sample data:

  • Data Set (Google Sheets) for both DS1 and DS2
  • Google Data Studio report with these sources and a filter on Country. The Country filter only filters the top table. I want it to filter the bottom table based on the IDs visible in the top table.
Solution

  • One approach is to blend the respective ID fields in both data sources and then use a fixed-size list control to show the Country values; to filter the data to show all records for UK set the default selection to UK (or select as required):

    1) Blend Fields

    Description Table 1 Table 2
    Data Source: DS1 DS2
    Dimension 1: ID DS1 ID
    Dimension 2: Name Make
    Dimension 3: Age Model
    Dimension 4: Country
    Dimension 5: temp
    Image: table_2 table_2

    2) Join Configuration

    Join Description Table 1 🔗 Table 2
    Join Operator: Left Outer
    Join Condition 1: ID (Table 1) 🔗 DS1 ID (Table 2)
    Image: join_configuration

    This will result in a blend with 7 fields:

    Fields
    Name
    Country
    Make
    Model
    ID
    Age
    temp

    3) Fixed-size list Control

    8

    Description Field
    Control Field: Country
    Default Selection: UK (leave blank to view all values by default)
    Metric:
    Aggregation:    		 ID
    AVG

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

    12