Calculated field in blended data

Looker Q&A Looker Studio
, , , ,
1

Hello!

I have a dashboard with three data sources, each from a different platform. In it, I display several tables with the data from each platform so that we can compare the information.

This data is all grouped by person, showing the activity each one had on the platform. Since the user’s email is the same across all sources, I would like to have a single dropdown filter at the top of the screen with the emails to filter the entire dashboard. It’s worth noting that I cannot unify the data sources into a single database, as each one works differently.

My obvious initial idea was to create a blend of these three data sources and use the COALESCE formula. I first tried to create a calculated field with this formula in the data source, but I saw that wouldn’t be possible. So, I then tried to use the formula in the filter’s control field, but that didn’t work.

Could anyone help me achieve this result?

2

It’ll be tricky to work around this limitation. Could you populate a separate list of all emails in another source, like a google sheet? If you can do that, then you can add that sheet to your blend, and then filter on that email dimension without having to use a calculated field.

Gemini gave me back three solutions for populating the sheet and keeping it up to date. None of them are perfect, but I figure I’ll share them with you.

1. Semi-Automated Update via Scheduled Email Report (Least Technical)

This method simplifies the periodic manual update process by having Looker Studio do the heavy lifting of gathering the current emails.

The Workflow:

  1. Create an Email-Gathering Report: In a separate, dedicated Looker Studio report, create three simple tables (one for each of the three platform data sources).
  2. Configuration:
  • Set the Dimension for all three tables to the Email field.
  • Set the Metric to Record Count.
  • Set the Rows per page to a very high number (e.g., 50,000) to capture all emails.
  1. Schedule the Delivery: Use Looker Studio’s Schedule email delivery feature to send this report to yourself (or an administrator) daily or weekly.
  2. Update the Key Table: The recipient simply has to copy the data from the three attached tables in the email, paste it into the master Google Sheet, and then run the “Remove Duplicates” function in Sheets.

Pros: No complex scripting or coding required.
Cons: Still requires a quick manual step (copy/paste and deduplicate) upon receiving the email.

2. Automated Update via Google Apps Script (Recommended) :robot:

This is the most common and robust automated solution within the Google ecosystem, using Google Apps Script to read and write data.

The Workflow:

  1. Create a New Google Sheet: This is your “Shared Email Dimension” sheet.
  2. Write the Script: Use the Google Apps Script editor (found under Extensions > Apps Script in the Sheet). The script would perform the following steps:
  • Connect to Data Source 1, 2, and 3 (if they are also Google Sheets or BigQuery).
  • Read the email column from all three sources.
  • Combine the results into a single array.
  • Use JavaScript methods to deduplicate the array.
  • Clear the old data from your “Shared Email Dimension” sheet and write the new, deduplicated list.
  1. Set a Trigger: Use the Apps Script Triggers panel to set the script to run daily or hourly automatically.

Pros: Completely automated. The Looker Studio filter will always reference an up-to-date list of emails.
Cons: Requires basic knowledge of JavaScript/Google Apps Script to set up.

3. Automated Update via BigQuery (Most Scalable) :cloud:

If the original three data sources can be loaded into BigQuery (Google’s data warehouse), this becomes a purely SQL-based, fully scalable solution.

The Workflow:

  1. Load Data: Ensure all three platform datasets are loaded into separate tables within BigQuery.
  2. Create a View: Create a BigQuery View that simply selects and unifies all unique emails from the three tables using a UNION DISTINCT query.
UNION DISTINCT
SELECT DISTINCT email FROM platform_2_table
UNION DISTINCT
SELECT DISTINCT email FROM platform_3_table
  1. Connect to Looker Studio:
  • Connect Looker Studio directly to this BigQuery View.
  • Use this new BigQuery data source (the View) for your filter control.
  1. Cross-Source Filtering: Follow the original steps for Cross-Source Filtering, using the BigQuery View as the filter control source.

Pros: Fastest, most scalable, and automatically updates in real-time as the underlying BigQuery tables change. No external scripting is needed.
Cons: Requires moving your data into BigQuery, which may involve cost and architectural changes.