Usually, using a parameter to filter a graph is better than simply creating a filter on a dimension because not only can we filter our charts, we can also use it in many other places in the marks card as well as use the value of the parameter in various text fields throughout the dashboard. However, one of the drawbacks of using a parameter is that only one item can be selected at a time. This means that if we had a parameter on category in SuperStore, the parameter could only ever be ‘Technology’, ‘Furniture’, OR ‘Office Supplies’, never ‘all’. What if I told you that didn’t have to be the case and there is a way to have an ‘All’ option. Crazy, right? In this blog, I will be showing you how to achieve exactly that! Let's begin!
For this exercise, I will be working with SuperStore data and I will be recreating the exact example I described above, meaning our parameter will be filtering by Category. Before we do that though, we need to build a chart as a starting point. To build the chart below, place Sub-Category onto rows, sum of Sales onto the columns, and sort the Sub-Categories according to their Sales totals. Once complete, simply follow the steps below and you will have a parameterized filter with the option to show ‘All’ values!
Step 1) First we will build the parameter. Usually in these situations, you will create a string parameter and allow a list of values equivalent to all unique values in the dimension. For Category, that would mean adding ‘Furniture’, ‘Technology’, and ‘Office Supplies’ to the allowable values list. In this scenario, not much has changed! In fact, all we need to do differently here is add a 4th value for ‘All’.
Step 2) Now, we will write our Calculated Field that will act as our filter. Normally, you would create a simple boolean calculation that says [Dimension] = [Dimension Parameter]. In this case however, we need to add something to allow All values when ‘All’ is selected in the parameter. In order to do this, we are going to add the clause seen in the picture below on the right. In layman's terms, this clause will pass through every possible value when ‘All’ is selected. Also, don’t forget the ‘END’ at the bottom of the new calculation!
Step 3) Now, we will place our calculation into the Filters Shelf, select ‘True’, and voila! You now have a parameterized filter with the option to select all values