Data Viz Done Right

September 15, 2012

Tableau Tip: Adding an “All” filter option to a string parameter

21 comments

I always was under the assumption that you could not have an “All” option in a parameter to make it act like a compact list filter, but I was wrong.

I was working with a n00b Tableau user at work answering his questions.  He was building a tag analysis dashboard and was using a parameter that listed all of the tags, but then had an “All” option at the top.  I completely changed the discussion and asked how he did it, and it’s so simple.

Step 1: Add the field you want to filter to the Rows shelf.  I’m only doing this so that you can see how it works along the way.  I’m using Sub-Category.

Step 2: Right-click on the field you want to use as a filter and choose Create Parameter. The advantage of right-clicking on the field you want to build the parameter on is that the list of values is pre-populated.  Add (All) to the list of values.  I use the format (All) so that the parameter looks like a compact list filter.  I then rearranged the order so that (All) is at the top of the list.

image

Step 3: Right-click on the parameter and choose “Show Parameter Control”.  It should pop up on the right side of the view.

Step 4: In order to use the parameter as a filter, you have to create a calculated field. 

Essentially what happens is that if the user picks (All) there’s nothing to filter, so it shows all sub-categories.  Iff the user picks a sub-category, then that is passed to the filter.

image

Step 5: Drag the Sub-Category filter to the Filters shelf and select the True option.

image

That’s it!  To see it in action, pick something from your parameter, then change it back to (All).  So super cool!

Literally about 1 minute of work.  I know I’m going to use this technique many, many times now.  Thanks to the n00b that showed me the light!  I love learning new tricks.

Download the sample Tableau workbook here.

21 comments :

  1. A better way to write your "Sub-Category Filter" formula would be:

    [Sub-Category Parameter]="(All)"
    OR
    [Sub-Category Parameter]=[Sub-Category]

    This does the same thing, but is more efficient for the underlying data source (it is easier to read IMO and involves less typing).

    This change can have a impact on performance, especially when dealing with large data sources.

    ReplyDelete
  2. Thanks for the helpful details on adding an "all" filter.

    ReplyDelete
  3. What will happen if we have [Sub-Category] present in two different database tables?

    ReplyDelete
  4. andy - if im using a parameter as a filter, would it allow me to do "relevant values" on another filter based on this?

    ReplyDelete
    Replies
    1. Vaidy, here's an example from Jonathan Drummey. http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/

      Delete
  5. This is great. Thanks! I am trying to first filter a data with 2-3 variables and then get all records from another data for the customers who satisfied those filters in the first data through custom sql. I included 'ALL' in the option while filtering in the first data, but if I select ALL in one of the variables it's not getting any data from the second data using custom sql. Could you plz help? Thanks!

    ReplyDelete
  6. Many thanks for the tips! Was looking for a way to do it and you solved my problem :)

    ReplyDelete
  7. Thanks for the tip, this was very useful!

    ReplyDelete
  8. Thank you very much Andy, It helped me a lot.

    ReplyDelete
  9. Hi Everyone,
    I want only 2 values instead of all values
    Example: Labels and Envelopes
    Here, All should be Labels + Envelopes. Not every value in the column

    What should be the calculated field be in this case.

    -Nishant

    ReplyDelete
    Replies
    1. Created a context filter for the same column which included only labels and envelopes. Thanks for the post Andy :)

      Delete
  10. Hi, I'm having issues setting up the user permissions for one of my reports.

    I need to have only certain groups be able to see the 'Total/All' perameter.

    Can anyone please help me with this?

    ReplyDelete
    Replies
    1. Gunner, parameters themselves are not configurable at the user level. The workaround would be to create two different parameters and two different views and only show the worksheets/dashboards for that particular user.

      Delete
  11. Thanks! Just a follow up question: How do you get the All filter to sum and display a total instead of the individual components?

    ReplyDelete
    Replies
    1. A total calculation and a different dimension in play in the view.

      Delete
  12. Nice Post Andy, Really helpful

    But i don't think so it will work for other data types ? Is it ?

    ReplyDelete
  13. Just wanted to leave a comment to let you know this post really got me out of a bind today. Thanks!

    ReplyDelete
    Replies
    1. That's great to hear...thanks for sharing!

      Delete
  14. what about if the value of the parameters is not in the sub-category?
    For example
    value of parameter =1, it will show Books, value of parameter =2, it will show Labels etc...

    ReplyDelete
  15. Save my day, thank you so much Andy!

    ReplyDelete