September 15, 2012
Tableau Tip: Adding an “All” filter option to a string parameter
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.
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.
Step 5: Drag the Sub-Category filter to the Filters shelf and select the True option.
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.
A better way to write your "Sub-Category Filter" formula would be:
ReplyDelete[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.
Thanks for the helpful details on adding an "all" filter.
ReplyDeleteWhat will happen if we have [Sub-Category] present in two different database tables?
ReplyDeleteandy - if im using a parameter as a filter, would it allow me to do "relevant values" on another filter based on this?
ReplyDeleteVaidy, here's an example from Jonathan Drummey. http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/
DeleteThis 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!
ReplyDeleteCan you share your SQL?
DeleteMany thanks for the tips! Was looking for a way to do it and you solved my problem :)
ReplyDeleteThanks for the tip, this was very useful!
ReplyDeleteThank you very much Andy, It helped me a lot.
ReplyDeleteHi Everyone,
ReplyDeleteI 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
Created a context filter for the same column which included only labels and envelopes. Thanks for the post Andy :)
DeleteHi, I'm having issues setting up the user permissions for one of my reports.
ReplyDeleteI need to have only certain groups be able to see the 'Total/All' perameter.
Can anyone please help me with this?
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.
DeleteThanks! Just a follow up question: How do you get the All filter to sum and display a total instead of the individual components?
ReplyDeleteA total calculation and a different dimension in play in the view.
DeleteNice Post Andy, Really helpful
ReplyDeleteBut i don't think so it will work for other data types ? Is it ?
Just wanted to leave a comment to let you know this post really got me out of a bind today. Thanks!
ReplyDeleteThat's great to hear...thanks for sharing!
Deletewhat about if the value of the parameters is not in the sub-category?
ReplyDeleteFor example
value of parameter =1, it will show Books, value of parameter =2, it will show Labels etc...
Save my day, thank you so much Andy!
ReplyDeleteAs valid a tip as it was five years ago. Just applied this to fix a horrible boolean filter on a dashboard. Fantastic!
ReplyDeleteSweet! Glad it's still helping!
DeleteI am facing an issue of similar kind. I have 3 different fields from my data source as Primary_Vendor, Secondary_Vendor and Tertiary_Vendor. Also I have the data related to how much they charged for the customers. Sometimes Primary, Secondary and Tertiary Vendors or any two of them may be same for a customer. Now I need to display the Vendor_Name Vs Charges when Vendor_Type is Primary, Secondary, Tertiary and also "ALL". I have created a Parameter "Vendor Role" with a list of 4 String values i.e., Primary_Vendor, Secondary_Vendor, Tertiary_Vendor and "ALL". Then I have created a Calculated field called "Vendor_Name"(dragged it to Row Shelf) using case statements to choose the type of vendor appropriately for Primary, Secondary and Tertiary. But I am looking for a logic for "ALL" option which selects all the vendors as your above suggestion not able to help me.
ReplyDeleteI'd suggest you post this on the forums. Honestly, I don't have the bandwidth at the moment to investigate this for you and I'd hate to see you waiting too long for a response. Good luck!!
DeleteThanks so much Andy, your instructions were very clear and this tip made my report a lot more useful and impressive!
ReplyDeleteThank you very much Andy! This was just what I needed today!
ReplyDelete