How to build a control that filters blended data in Looker Studio?

Yet again, we'll be talking about blended data. These are powerful, but sometimes frustating. You've been playing with SUM(IFNULL, with COALESCE to build custom fields and still, you didn't find a way to build a control that filters blended data in Looker Studio / Data Studio?


Good news, after months of research we couldn't find a solution, but Thomas Langnau shared one with us, thanks for that buddy!


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


Controls usually don't work with blended data

In this example, we'll be using a sample Google Ads data source blended to a sample Facebook ads data source, looking at impressions by date (full outer join on date):

looker studio blended data control filter join

Now, you want to have a nice table that gives you total impressions by date.


At this stage if you've landed here we think you know how to do it:

  • Dimension: COALESCE(Date (Facebook),Date (Google))
  • Metric: SUM(IFNULL(Impressions (Facebook),0)+IFNULL(Impressions (Google),0))


Once you've done this, you've got a nice table that gives you total impressions facebook + google by date. Excellent.


But what if you wanted to filter only on facebook or google? You've been looking for ways, and none worked. Now, it's time to show how we did to build one.


The key: adding fields in both data sources that share the same ID

Please Edit both your data source, and add a new field:

  • For the facebook data source, we'll add a field called source, ID sourceID, and just input 'facebook'
  • For the google data source, we'll add the same field, this time inputting 'google'
  • It's very important that both fields share the same ID
looker studio blended data control filter new field

Now, this is where the magic happens:

  • Modify your blended data to insert both sourceID from both tables as dimensions, and join on them
  • Create a control with the following formula: COALESCE(source (Facebook),source (Google))


It looks good, but it doesn't work right? We know that, no worries, we're going to fix it for you :)

looker studio blended data control filter both source

Add two new fields to each data sources

Go back to your two data sources, and hide the two field source (by clicking on the 3 dots right to the name, and then Hide).

The control should break, and this is exactly what we want it to do :)


Now, look at the formula in your control, and copy the two weird names (the IDs in reality) you'll see:

looker studio blended data control filter getting the IDs

From there, that's pretty simple:

  • Go back again to your two data sources, and un-hide the source fields
  • Create two new fields in each data source, the first one called source0 with ID sourceID_dv0, and value: Source
  • Similarly for the second one, call it source1 with ID sourceID_dv1 and value: Source
looker studio blended data control filter 2 new fields

And that's it, it just works, mind blowing right?


Remember, the key here is to create two additional fields with the parameter IDs we've seen in our control when we've hidden the original fields. Weird, but it does the job!

In this article, we've reviewed how to build a control that filters blended data in Looker Studio.

PROBLEM SOLVED !


Previous article: How to group blended data by week or month in Looker Studio?

Next article: How to use Dataddo, the third-party app that offers free connectors for Looker Studio

Looking for something else? All our articles can be found on our blog page