This post, like many, started from a conversation on Twitter. I was patrolling for interesting Data Studio tweets when I saw a frustrated person with a request for the addition of a “slicer” to the tool. I had only just started my first coffee of the morning, so I of course thought – ‘Ah, some nube looking for a slider range selector. Poor guy!’ Fortunately, I have learned to never listen to myself before I have had at least two mugs, and never underestimate my own ignorance. I thought I should at least lend a sympathetic ear. So, I brilliantly replied “Slicer?”
The poster was gracious in rectifying my ignorance. ” A slicer is an interactive control. We use them in Power BI. You can use them in Excel too.” Oh. Well. The least I could do was check it out as I, like everyone, have strange holes in our experience and Power BI is one of mine. So I did a quick YouTube search as I got ready for work and got an overview of that awesome little widget.
If you’re familiar with the slicer, I won’t bore you – Let’s just say it is a powerful tool that does what dynamic filters do in Data Studio and much more. It does many things I would like to do as well so I made a note to return to the conversation and went on about my business. I ended up pulling an all-nighter getting out my last blog post so I didn’t respond til a couple days later.
We dug in a little deeper – Data Studio can do several things a slicer can do with dynamic filters, but it doesn’t have a slider feature to allow you to select a range of values. That is something on the development backlog and I hope it becomes available in the future. But, I had an idea that would not let me go, and I really wanted to help.
I also realized later that the original tweet came from Zaine Clark @ZaineClark, a Senior SEO associate at Seer Interactive (a very venerable SEO and analytics group) and he was serious about finding a solution. Note to self: Try checking someone’s profile before you respond on Twitter!
Anyhow, here was my thinking … Data Studio recently released a feature allowing chart filtering. It works great on time series graphs. You can click and drag to filter for a range of dates as in the example below.
The instructions for using this new feature are still a little vague. I have only seen it used on time series, but I didn’t see that it could not be used for other graphs so I thought it could be twisted a bit to do other stuff. After some experimenting, I found this was correct, but with some limitations. As far as I can tell, it only works on the X axis of the charts and is limited to Dimensions. This makes things a bit tricky if you want to select a range of values that is actually a metric!
Fortunately, a recent tweet from Evan Levy @EvanLevy, describing the trick to manipulate date formats, and some other tricks I had picked up on my day job building and embedding product selectors in Data Studio gave me enough to go on and give me confidence that some solution was possible.
How to create a slider control in Google Data Studio
First, you have to convert metrics numbers into a text dimension. Note that this does not work for aggregated metrics (at least not directly). So, let’s use the example of the number of characters in a query coming from Google’s Search Console connector.
- Create a new metric using a standard function called ‘QueryLengthNum’. This gives us a non-aggregated metric.
- Create a new dimension by casting QueryLengthNum to text and call it ‘QueryLengthChar’. You could have done this in a single step, but you need both fields! CAST(QueryLengthNum As Text)
- Create a new metric called ‘Always1’ with a constant value of 1. You can’t do this directly so use this hack: QueryLengthNum/QueryLengthNum. If you want a different constant just add the value to 1!
- Create a line graph with these values: Dimension = QueryLengthChar , Metric=Always1 (rename to ‘Length’ , Sort=QueryLengthNum Order=Ascending, Interaction=Apply Filter
- Change the style: Line Thickness = 5 , Color = White , Number of points = 100 , Left Axis Min=Blank (sets to ‘auto’) Background Grid = Dark Blue , Legend = No Legend
- Shrink the graph so only the white line and the background show – the left axis values will disappear! Stretch to desired length.
- Create a table to be filtered by the slider – see example below. Hit view and play with your new slider!
So, there you have it: A slider hack that reproduces some of the capabilities of a Power BI slicer. You can check out the finished example. There is even a dynamic data source selector so you can try it with your own data.
It’s not a perfect solution, and working around aggregated metrics can be a pain – If you are looking for the average rank position as a non aggregated metric you can use the Supermetrics premium connectors so you can build an average position slider as seen here. If you are interested in Supermetrics connectors try this page!
Here is an example with a slider that uses the Supermetrics connector and some extra fields to jazz up the user interface a bit.
If you have suggestions (especially workarounds for aggregated metrics!) please leave a comment!