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?

user_3383907 0
Viewed 650

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.
google-data-studio
1 Answers

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

user_128925530
Related