Dynamic Top Count Filters Using Variables and Parameters
By Jason Picker , Sales Engineer
Top Count filters are easy to implement using the Quick Filter menu available off the right-click menu. However, there are times you need to create something more flexible. In this blog, you will see how to use a variable to alter the count and a parameter to change the measure that the filter is based on.
Step 1: Create Top Count Filter
Let’s start by creating our Top Count filter. In my example, I will create a Top 10 filter on Product Subcategories based on Sales.
Creating Top Count Filter
Step 2: Convert Count to use a Variable
Next, we will modify the count to use a variable to prompt the user for the number of items. This is accomplished by clicking on the Row Filter button on the Query tab and then clicking on the purple x icon to build the variable.
Creating Top Count Variable
Once you save the variable settings, you will notice that the Top count is replaced with the variable name.
Filter Dialog After Creating Variable
Step 3: Create Measure Parameter
Now comes the fun! We are going to create a parameter of the measures we want to use as our filter options. On the Elements tab of the Selector, you can click on the gear icon in the upper left corner and choose the New Parameter menu.
Create Parameter Menu
Give the parameter a name, select the measures you want, select the default value and then press the OK button to save it.
Create Measure Parameter
Step 4: Change Filter to Use Parameter
Let’s put it all together now. We will edit the Row Filter and change the measure selection to use the parameter.
Modify the Row Filter
To wrap up my report, I changed my slicers to use buttons so it looks more consistent. We now have a fully dynamic report that gives our users flexibility in what they want to see. Enjoy!