June 7, 2012
Create a global filter in Tableau across multiple data sources in eight simple steps
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.
Put All into your Parameter.
DeleteFix your calculated field to say the following [Location]=[Parameter].[Location] or [Parameter].[Location]="All"
Cheers,
Tyler Garrett
Senior Business Consultant
Tableau Software
Dmitri,
ReplyDeleteYou cannot have an All option in a parameter. If you had that, wouldn't it then be a filter. :-)
Andy
False. You can add an All to this.
DeleteAndy,
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.
DeleteI'm also experiencing the same issue here. I have Tableau 8 and although it does allow me to pick the sheets to a specific filter it only allows me to pick sheets that are using the SAME data source. Any work arounds for this would be greatly appreciated.
DeleteGlobal Filters that have Relevant cascading between the filters. For example: Filter for Region: Northeast would only show Filter for States that are located in the Northwest region not all States.
Thanks!
Marina
Blend the two data sources together and the filter will flow through to the secondary data source.
DeleteThanks for your response, Andy! I will definitely give Data Blending a try!
DeleteHI Andy,
ReplyDeleteI am new to tableau. M facing issues creating parameters. I want a single filter (happens to be text) to affect all the sheets (having multiple data sources) in my single dashboard.
Could you please help?
Thanks
Ayushi
Also, m using Tableau 8
DeleteAyushi, if you can provide a sample workbook with your situation, that would be helpful.
DeleteThis was very helpful! Thanks
ReplyDeleteDoes anyone have any guidance on how to make a filter that is using multiple measure names apply across multiple worksheets? The filter works great on allowing the user to display different measures, but I have three different views coming from the same data source and I want to apply the filter across all three views in the same dashboard.
ReplyDeleteCliff, can you provide a sample workbook?
DeleteProbably you can try to use both Show/hide view options here.
DeleteHere are the steps you can follow:
1. Create a parameter "Choose Measure" with various measure names For example, if you want to analyse measures such as "Sales", "Profit", "Expenses"; add these options.
2. I believe, all work sheets are using the same data source. So create a calculated field "Selected Measure"
3. Now use "Selected Measure" calculated field in as a measure in place of sales / Profit / Expenses
4. So, you can pull the work sheets in a single dashboard
5. Add parameter to Dashboard for changing the measure.
6. As you change the measure, the dashboard with the work sheets will use selected measure.
Thanks
Gowtham
+91 950 580 3767
This does not work on tableau stories. It only works on tableau dashboards.
ReplyDeleteHi Experts,
ReplyDeletePlease let me know, how to achieve the below in Tableau 8.3 or above.
Two Data Sources A and B, one work sheet:W1 has used both this A and B to represent View.
Data source A has Role Column and Data source B also has Role Column.
Another sheet:W2 is created with Data source A - taking Role in Row shelf.
Now, in dashboard D1 : has two worksheets W1 and W2. Now we have to use W2 as Filter - to filter out the data in W1.
I have just clicked on Use as Filter in W2, but when role is selected, it filters the data from primary data source i.e. A , but data from Data source B is not filtered accordingly..
Thanks in advance
Poornima
Please provide a packaged workbook and I will take a look.
DeleteHow can we upload workbook here
DeleteUpload it to tableau public, Dropbox, Google drive, anywhere really and post the link here.
DeleteHi,
DeletePlease find the below link : http://community.tableau.com/message/369071#369071
please let me know why i am not able to filter the data in secondary source also.
thanks
Poornima
Hi,
ReplyDeleteThe above works well for an ordinary workbook but when I extract the data and export to a packaged workbook, the filter stops working. Does anyone have any idea why this happens/how to fix it?
Thanks!
Danny
Andy...question on this. I use this often but I've come across something interesting. In my home dashboard page I show 6 metrics that go across multiple data sources that don't lend well to blending. I want to set up an action that will pass the values I've selected in my parameter to a target report on another tab that does not work off the parameter. To do this, I created a field that = [Parameter] which works pretty good. The problem I'm facing is I have have the (All) option in play...Do you know of a way to pass that (All) over in a way that will tell the target report NOT to filter on anything.
ReplyDeleteThe reason I'm doing this is to give the user greater slice and dice capabilities on the target page to deep dive a particular metrics. Since I want to have multi-select on the deep dive filters and to show relevant values only, I don't want to tie the targets to the parameters.
Hope this makes sense.
Kevin
Kevin, if (All) is an option in your parameter, then change your calc to
Delete[Dimension]=[Parameter] or [Parameter]="(All)"
and then choose True when you add this to the filter shelf.
Hi,
ReplyDeleteCould you please tell me the calculations needed to add All as an option in above example.
Thanks
Hi Nidhi,
DeleteAlong with other steps, you can add "All" as an option in Parameter list of values. And then in the calculation fields you can make a check whether the parameter value is "All" or not. If the selected value is "All" you can return "True". This makes the Parameter with "All" implementation. Hope this helps.
This post outlines how to add an All option to a parameter. http://vizwiz.blogspot.co.uk/2012/09/tableau-tip-adding-all-filter-option-to.html
DeleteHi Andy,
ReplyDeleteI have 2 different column in the same file (or i can create a new column separately for the same data) which has same data. I want to create a only one filter and it should be filter from both the tables. Any leads?
Thanks in advance
if the data gets refreshed then new data does not appear in the parameter
ReplyDelete