How can I apply a filter using a calculated control field against blended data?

user_3383907 0
Viewed 145

When I create a filter using a calculated control field against blended data, the filter does not work.

I have added table1 and table2 from my source to the report. I created a blended table by combining them on ID.

I add a table chart to the report using the blended table as the source.

  • I can filter using the blended table's ID field
  • I can filter using the blended table's name field
  • I can filter using the table1 data source and creating a calculated control field using: CONCAT(name, " (", ID ,")")

But, I cannot filter using the blended table and creating a calculated control field using: CONCAT(name, " (", ID ,")")

This looks like a bug to me?

google-data-studio
1 Answers

Yes, was able to replicate the issue (chart-level calculated field in a blend not working as expected). For future reference on the status of the issue, a bug report titled Bug with creating calculated control fields in filters against a blended data was created on 30 Jul 2022 with the details in the question.

In this case:

  • Join operator: Left Outer
  • Required fields: In the 1st (left) table

Therefore one workaround is to create the calculated field in the blended data source (implemented in the 10 Jun 2021 update), which filters values as expected:

CONCAT(name, " (", ID, ")")

blend_formula_dimension_3

The complete process:

1) Blend Fields

Data Source: table1 table2
Dimension 1: ID report ID
Dimension 2: name ID
Dimension 3:
Formula:		 name (ID)
CONCAT(name, " (", ID ,")")		 score
Image: table_1 table_2

2) Join Configuration

Join Description Table 1 Table 2
Join Operator: Left Outer
Join Condition 1: ID (table1) ID (table2)
Image: join_config

The blend will create 5 fields:

Fields
name
name (ID)
ID
report ID
score

3) Control

Description Details
Type: Drop-down list
Control field: name (ID)

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

gif

user_128925530
Related