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?”
tl;dr skip to the solution!
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
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.
Update Feb 16, 2019
Data Studio craftsman Riccardo Zagaglia (Twitter @zagoz) just contacted me with the news that his latest creation, NBA 2018-2019 – Update pre-All Star Game, was produced with slider selectors using the methods I have outlined here. I’m not a big basketball fan, but the interactive report is really cool! Click the link above to see it in action!
If you have suggestions (especially workarounds for aggregated metrics!) please leave a comment!
Hello.
First I have to say that you have an excellent blog. So, thank you for sharing such interesting information.
The “Slider control” seems to me a perfect solution. But I can’t make it work. Please, you can share a Data Studio panel only with the “Slider control” so you can extract and copy the formulas.
Thank you very much for everything.
Hi Pau, Thanks for the comment and glad you like it!
I will try and do that and contact you when I have – or if you are on Twitter you can reach out to me. I sometimes do a Google hangout with people to help them out.
Hi, Lee.
Thank you very much. Unfortunately, I don’t know much English (I commented on it by Google hangout) but of course, I already have you on my Twitter list. But don’t worry, in the end I will find the solution.
Just so you know, when I subscribe to the article comments, at the end it takes me to the URL of http://helpfullee.com/slider-selector/ without the SSL Certificate, and does not load the article. I leave an image to understand it better https://ovacen.com/wp-content/uploads/2019/02/subscribe-lee.jpg Something happens with redirection!
Greetings
Thanks again Pau! It looks like I have a redirect issue! I hate those! I will try to get that fixed and let you know. I really appreciate you letting me know. Gracias!
Hi,
thank your very much for your post!
I am trying to build a slider by using metrics such a conversions. Is that even feasible? If yes, how should one proceed?
Thank you for your help,
I appreciate it.
Arben
Hi Arben,
There is no direct way I know of to do the metric slider, unfortunately. You have to turn your metric into a dimension – so values become bucket labels, basically. Then you should be able to use the bar or line charts with the interactivity turned on.
Check out Riccardo Zagaglia dashboards on NBA stats – several of his use this method – https://datastudiodashboards.blogspot.com/
Hi Lee, I just found your blog and want to thank you for posting these helpful tips and tricks!
I have a table of article titles with a filter control for the titles. I am wondering if there’s a way to search keywords and have the table return all values that match the words in no particular order.
e.g., if I search “how to 2019” I would like it to return the value “How To Improve Your Backlink Strategy In 2019”.
I have tried using “contains”, but it only matches values that contain the phrase in exact order. I also tried “regexp” (e.g., “how to|2019”) as well as “in” but they both return much-too-broad results, matching values that contain only one of the keywords.
Do you know of a workaround that could help me?
Thank you,
Angie
i want to use slider for date range, so i converted date into dimension and considered metric always 1, it does not work
can you let me know the possible solution
Hi, thanks for your hack and sharing with us. Please could you elaborate on the steps?
What is QueryLengthNum? Command where? I cannot find it anywhere. Then what is a line graph in Data Studio? Do you mean Time series chart? Sorry but most of your steps are hard to follow.
Hi Mr.Hurst,
This is a great tutorial, however I cannot make it work for my project. I have a data that population and income of cities and I want to make a slider selector to filter data.
Your article seems that it can be done.
Do you have a video tutorial where I can follow so that I can apply it to my project.
Thank you very much.
Thanks Brody, I don’t think I have a video yet, but it seems like there is interest so I will review this and make one.