I have a dashboard with 2 charts showing data from different sources (both are google sheets). How can I create dropdown controls that would filter both tables?
As a simplified example;
My first data source has the columns “date”, “city” and “degree” and all three columns are used in a Looker Studio dashboard table.
My seond data source has the colums “date”, “city” and “hobby” and all three columns are also used to create a separate table in the same dasboard.
In addtion to these 2 tables I want to have 2 drop down filters for “date” and “city”. These drop down should show all available options from both data sources ie even if a “date” (or “city) appears in only one of the sources, the drop down should show all dates (or cities) from both sources.
When the drop down filter is applied, only those records with matching “dates” (or “cities”) should be showing in both tables.
Has anyone had success in creating this kind of set up?
The solution offered in the link you provided works but only if the same “date” (or “city”) value already exists in BOTH data sources. If a specific “date” only exists in one of the data sources, it does not appear in the drop down. I would like the dropdown to display all “date” (or “city”) options in both data sources and apply any drop down filter selection, to the 2 separate tables.
Thanks - I have read this but it seemed to only provide a solution for applying a single control to a single table based on a blended data source, whereas I would lke to apply a single control to 2 separate tables based on 2 separate data sources,. Or am I missing something?
Here’s the trick: If you want to have a control that filter both tables will all dates and all cities listed, you need to blend these two tables first (in your case, Full outer join on both city and date), make the manipulation to create 4 more fields (date_dv0 date_dv1 city_dv0 & city_dv1 on both data sources), and then the dropdown will be on your blended data doing COALESCE(date_dv0,date_dv1). Now, when you apply a filter on this, it will also impact dataviz based on original data sources, not only dataviz based on blended data source. Hope it’s clear. If not, maybe you could share with me your report in Edit mode and I do the configuration in 2 minutes, shoot me a direct e-mail and I’ll take care of it.
Thanks, I’ve tried the following steps, in this case ignoring “city” and just trying to create a single “date” filter that I want to apply to the two tables. Can you let me know else I need to do?
Create 2 looker studio dashboard tables based on data source 1 and data source 2. (these will be the 2 tables I want to filter)
Create blended data source with just dimensions to be used for drop down filter (in this case “date”), outer joined from both sources.
Create dropdown-list control based on blended data and using coalesce(data source 1 “date”, data source 2 “date”) formula in the control field. Dropdown filter now shows all dates from both sources.
Hide “date” field from both data sources (=>drop down filter shows as error)
Copy the 2 field ids showing in coalesce formula
reinstate date field in both data sources
Created new fields in data source 1 and data source 2 using the 2 field ids taken from coalesce formula. Both new fields pointing to the respective “date” field in each source.
Result:
I have a dropdown filter that shows all available dates, but selecting a date does not filter either table.
Should work, hum… when you created the new fields, did you make sure to use the name (like date_dv0 and date_dv11, but could be some random like ggse_dv0 & ggse_dv1) for both the field name AND the field ID?
Thanks , I tried making sure the field ID from the coalesce formula was used for both the Field Name and Field ID for the new fields I created in each data source, but this did not make any difference. I’ve also tried setting everything up from scratch again, but unfortunately the result was the same - I will try again next week.
To be sure just did it, and works like a charm here: Untitled Report Would you mind sharing with me (via DM better) your report in Edit mode, so I can have a look? You can contact me @ Looker Studio Bible | LinkedIn
Thanks for sharing your file, I was able to see where I was going wrong. I was only creating one new field in each data source based on only one of the id’s used in the coalesce formula. But I saw that you had created two new fields (one for each field id) in each data source . (Total of 4 new fields)
Using this method I was able to create a drop down that applies to both tables. The only thing now though, is that I am unable to do this when trying to set up a date field as the drop down. (when using a text field it works). Are you able to replicate this?
As per the date, if everything is in date format, can’t you use the standard date selector? It will apply to both data sources, as long your date field is choosen in all your dataviz as the date field to be filtered on!
Thanks very much for your kind guidance in resolving this issue. This is a function that I will definitely be using in many of my Looker Studio dashboards.
