Data Viz Done Right

June 7, 2012

Create a global filter in Tableau across multiple data sources in eight simple steps

42 comments

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.

image

image

image

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.

image

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.

image

Step 2 – Make the Location field a Global filter on each data source

image

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.

image

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.

image

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.

image

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.

image

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.

42 comments :

  1. I am sorry, but I do not see the impact steps 1 and 2 would have. Why do you think those steps are needed?

    I 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.

    ReplyDelete
    Replies
    1. 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?

      I 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

      Delete
    2. Chris,

      Yes, 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.

      Delete
  2. Joe,

    Thanks 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!

    ReplyDelete
  3. Thanks for the good example.
    Could you please share how you can add an "All" option to the parameter?
    Thanks.

    ReplyDelete
    Replies
    1. Put All into your Parameter.

      Fix your calculated field to say the following [Location]=[Parameter].[Location] or [Parameter].[Location]="All"

      Cheers,

      Tyler Garrett
      Senior Business Consultant
      Tableau Software

      Delete
  4. Dmitri,

    You cannot have an All option in a parameter. If you had that, wouldn't it then be a filter. :-)

    Andy

    ReplyDelete
  5. Andy,
    Thanks. 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?

    ReplyDelete
  6. Dmitri,

    You 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

    ReplyDelete
  7. 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.

    ReplyDelete
  8. Hi,

    Thank 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?

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Hi Andy,

      Yes, 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

      Delete
  9. Hi
    Thanks 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  10. Hey ! 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!

    Thanks in advance.

    ReplyDelete
    Replies
    1. 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
    2. I'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.

      Global 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

      Delete
    3. Blend the two data sources together and the filter will flow through to the secondary data source.

      Delete
    4. Thanks for your response, Andy! I will definitely give Data Blending a try!

      Delete
  11. HI Andy,

    I 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

    ReplyDelete
    Replies
    1. Also, m using Tableau 8

      Delete
    2. Ayushi, if you can provide a sample workbook with your situation, that would be helpful.

      Delete
  12. This was very helpful! Thanks

    ReplyDelete
  13. Does 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.

    ReplyDelete
    Replies
    1. Cliff, can you provide a sample workbook?

      Delete
    2. Probably you can try to use both Show/hide view options here.

      Here 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

      Delete
  14. This does not work on tableau stories. It only works on tableau dashboards.

    ReplyDelete
  15. Hi Experts,



    Please 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

    ReplyDelete
    Replies
    1. Please provide a packaged workbook and I will take a look.

      Delete
    2. How can we upload workbook here

      Delete
    3. Upload it to tableau public, Dropbox, Google drive, anywhere really and post the link here.

      Delete
    4. Hi,

      Please 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

      Delete
  16. Hi,

    The 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

    ReplyDelete
  17. 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.

    The 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

    ReplyDelete
    Replies
    1. Kevin, if (All) is an option in your parameter, then change your calc to

      [Dimension]=[Parameter] or [Parameter]="(All)"

      and then choose True when you add this to the filter shelf.

      Delete
  18. Hi,
    Could you please tell me the calculations needed to add All as an option in above example.

    Thanks

    ReplyDelete
    Replies
    1. Hi Nidhi,

      Along 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.

      Delete
    2. 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

      Delete
  19. Hi Andy,

    I 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

    ReplyDelete
  20. if the data gets refreshed then new data does not appear in the parameter

    ReplyDelete