Property Controls are very useful for allowing consumers to affect values in an analysis file, like what to display on a given axis, but they do not work directly with marking or filtering. However, it is possible to hook property controls up to affect filtering by using an expression. Below we will explain how to do this for a couple different scenarios.
Assume you have a data table which contains sales data about stores in 4 different locations: Boston, Seattle, New York, and Los Angeles.
You may want to display a Line Chart which displays the count of customers' most recent purchases by month. NOTE: It can in fact be any chart, not just a Line Chart, but we will just use this for the sake of our article.
Next, we will need to create a Property Control. For this, we may want to create a multi-select List Box which contains values for our 'Store Location' column.
Then, we need to create our intermediary expression. This part gets a bit tricky so we will break it down into small steps. What we want to do is have the output of the expression be either 'Yes' or 'No'. Yes, if the value is selected in the property control and no if it is not.
We will need to use the find function to search to see if a value from the Property exists in the Store Location column. If it does, we output 'Yes', and if it does not ,we output 'No'.
If we only cared about single value selections from the Property Control, then it would be an easier expression, and we could do the following:
if(find([Store Location],'${whichStore}')>0,"Yes", "No" )
where ${whichStore} refers to the Property Control we just created.
If you want to allow the user to select multiple values in the Property Control, you need to extend your expression by using the $map preprocessor. Replacing ${whichStore} with $map("'${whichStore}'", "&") will give us the desired result. This is telling us to create a string where all the values in the whichStore Property Control are concatenated using the & character. So, if the Property Control has the values 'Boston' and 'New York' selected, the output from the map preprocessor would be 'Boston & New York'.
The find function will then search through each row in the data table, take the current row's value for Store Location, and see if it finds it inside the Property Control string (i.e. 'Boston & New York').
Below you will see we added this expression into a new calculated column called 'Store Location Property Control'.
Now that the column is created, we can adjust its filter to only show Yes.
The end result allows us to hide the Filter Panel and expose a single filter , created as a Property Control, via a text area. This will allow us to control filtering for the entire Filtering Scheme, including all Visualizations in all Pages attached to it. Of course, you can setup the Filtering Scheme to only work for a single page if desired as well.
Using Script Controls to do the same thing
Keep in mind it is also possible to accomplish a similar result using Script Controls. The downside of using Script Controls are that you need to create a button or link that will perform the filtering, there is no way to trigger it automatically when the Property Control is updated. The upside of using Script Controls are that you have access to more features in the User Interface to manipulate. For example you may want to update the Visualizations' title to describe which values are selected.
If we did this, we would not use the calculated column, rather we would send the property control values in as input to the Script Control, and we would use them to check or uncheck filters. Below is a screen shot of this script, where the property control values get sent in as a parameter called strSelection and the visualization we wish to update the title on gets sent in as a parameter called visual.
Property Controls to Filter a Single Visualization
What if you want to only control a single visualization and not all the visualizations in a Filtering Scheme? Then you would want to add a similar expression as a custom expression into the visualization, rather than as a calculated column. Let's take the same Line Chart as an example. The X-Axis shows us the Month of the most recent purchase, and the Y-Axis shows us the count of customers. Since the Y-Axis is showing us a number, we can replace this axis to show us the number of rows (customers), from the selected Store Locations (from our Property Control). The expression required to do this on the Y-Axis is:
Sum(if(find([Store Location],$map("'${whichStore}'", "&"))>0,1, 0 ))
We had to replace the Count aggregation with Sum, and we had to replace the 'Yes' and 'No' with 1 and 0.
