Global filters in Tableau are often one of your best friends, especially when building interactive dashboards that span multiple worksheets. However, if you have multiple data sources and multiple worksheets, global filters do not apply to all of your worksheets.
Yes, you can use actions, but this can easily become confusing for the user when switching worksheets. But wouldn’t it be great to have a global filter work across all of the data sources? Use this technique and you’ll get what you always hoped for.
In this example, assume you have three disparate data sources each with its own dashboard.
There is also a dimension named Location in all of the data sources. Note that this dimension does not have to be named the same in every data source for this technique to work.
At this point, if I add the Location dimension to the Sales worksheets and set it to a global filter, the filter will not get applied to the Shipping and Capacity dashboards because they are not from the same data source.
To make a “global filter” apply to all of the dashboards/worksheets, follow these steps.
Step 1 – Drag the Location dimension onto the filter shelf of one worksheet for each data source and choose the Use All option.
Step 2 – Make the Location field a Global filter on each data source
Remember, you must repeat steps 1 & 2 for one sheet of each data source.
Step 3 – Create a parameter named Location (or any name you’d like) with the following settings.
Of course, your list of values will need to be relevant to your data.
Step 4 – Create a calculated field that will determine if the Location dimension equals the value chosen in the Location parameter.
Step 5 – Copy/Paste this formula across all of the data sources. You should now have the Location Flag calculated field in the Dimensions list of each data source.
Step 6 - Drag the Location Flag dimension onto the filter shelf of one worksheet for each data source and choose True.
Step 7 – Make the Location Flag a Global filter on each data source
Step 8 – Show the Location parameter in each dashboard and place it on the dashboard wherever you prefer.
The Location parameter now acts like a Compact List quick filter. Your final workbook should look like this:
Choose a different Location, then flip to another tab. You will see the dashboard update based on the new Location. Change it back if you don’t believe me.
In this example, I did not want to allow the user to pick “All” as an option in the Location parameter. Instead I wanted them to be forced to pick only one Area. You could include an All option through a few more calculated fields.
I am sorry, but I do not see the impact steps 1 and 2 would have. Why do you think those steps are needed?
ReplyDeleteI would call this parameter based filtering. This parameter method is also limited to acting as a Single Select filter, and not as a Multi-select.
I notice you tagged this post with "data blending", but did not mention that as a route. I think data blending can produce a very flexible solution, enabling Multi-select, but does require additional effort and is custom to each situation, so not general steps.
Here is a slight detail to be aware of when using Independent Axis: Take a look at your Capacity Dashboard, with Area 1 selected, and when looking at Product D on the Trend view, without using the tool-tip, is the value 0% or 100% for every mark? The visual Tableau drew is misleading for this combination of data and chart settings in my opinion. It may be better to set it to automatic instead of independent and continue to not include zero, but that may not be the sparkline you are looking for. Another option is to turn off the zero line in formatting.
Hi Joe, I noticed your comment and am looking for exactly this, a Multi-select option that allows custom filtering on a blended data set. Any ideas?
DeleteI have two tables at different grains, so I want to use Tableau's data blend ability to associate them. The two link by a common key field (which is a foreign key in the second table). I need to show the distinct count of records in the second table--and display that in the GUI. But, that count needs to match the current filters and view of the first table (i.e. the count should be reduced by quick filters that affect the first table).
I've read blogs and watched videos, but I haven't yet found something that works. Any ideas are appreciated!
Chris Rains
Chris,
DeleteYes, there are a few ways to accomplish what you are looking for.
Please get in touch with me (you should be able to email from clicking on my name here), and we can setup a screen sharing session and walk through your options.
Joe,
ReplyDeleteThanks for pointing out that steps 1 and 2 are not necessary. I was repeating the steps I went through and hadn't thought about how the Location filter is not needed.
While data blending would be an excellent solution, especially since you're not limited to single select, my intent was to show an alternative method that worked well for the situation I had faced.
As for the axes, I simply threw the charts together without a ton of consideration for the design. My intent was to focus on the technique and not the viz itself.
Thanks for the comments and feedback!
Thanks for the good example.
ReplyDeleteCould you please share how you can add an "All" option to the parameter?
Thanks.
Dmitri,
ReplyDeleteYou cannot have an All option in a parameter. If you had that, wouldn't it then be a filter. :-)
Andy
Andy,
ReplyDeleteThanks. I was referring to your comment:
"In this example, I did not want to allow the user to pick “All” as an option in the Location parameter. Instead I wanted them to be forced to pick only one Area. You could include an All option through a few more calculated fields."
Basically, I would like to get something that would work as a multiple value filter across multiple data sources. Is this possible?
Dmitri,
ReplyDeleteYou can indeed have an "All" option in a parameter. Check out this blog post:
http://vizwiz.blogspot.com/2012/09/tableau-tip-adding-all-filter-option-to.html
Andy
I've used a similar method since I usually use multiple data sources on one dashboard. You can do a lot with calculated fields based on parameters. You can add an "ALL" or "NONE" value in your parameter edit window. This allows you to include all values, or to use the NONE value to collapse or expand a list (My default shows region and uses "NONE" TO collapse the facility and department levels. then the user can select a facility or department or 'Show Departments" and see it all. I've also been able to have one parameter OVER-RIDE the others which can be useful in a table.
ReplyDeleteHi,
ReplyDeleteThank you for nice posting. I have a question here. The dashboard is getting slower when using Parameter filter. I believe the parameter filter applies once all the data into Tableau. Initially without parameters using Global quick filters the report works faster. But with parameters its slower. Any idea on how we can improve performance or equal peformance of parameters as same as Global Filters?
Leeway, did you ignore steps 1 and 2? You should. That's an extra filter that's not necessary and will cause Tableau to run an extra query it doesn't need to run. See Joe Mako's comment above. I would expect a parameterized filter to be quicker than a global quick filter. Quick filters have always been a pain point for performance in Tableau.
DeleteHi Andy,
DeleteYes, I ignored those steps. And I found the issue of performance degrade. The parameter filter causing Tableau to generate bad query filters. I have corrected it. Dashboards are working good now. :) Thank you for quick reply back to my response.
Cheers
Gowtham
Hi
ReplyDeleteThanks for a helpful post, it be useful. Have you had any experience with a global date filter across multiple data sources? I am currently working on a project that requires this.
Thanks
Dominic
Dominic, you should be able to accomplish what you want using a date parameter than creating a calculated field that checks if the date parameter equals your date dimension. Do that for each connection and make them global.
DeleteHey ! Thanks for this. However, I want my parameter filters to work exactly like normal filters with the option of "Showing relevant filters". Is this possible? This has been a problem I've found hard to fix for quite sometime!
ReplyDeleteThanks in advance.
Parameters are static, so no you can't do that. In Tableau 8 you can pick the sheets that a filter applies to. That might be a better option for you.
Delete