Create Buckets for Continuous Values
Sometimes you may have a requirement to allow users to filter based on continuous values like a course grade, dollar amount, or age of a person. Theoretically, you could have an infinite number of values for a series of continuous value and this isn’t going to look great as a slicer, as seen below. If a user wants to filter the students based on those students with grades between 80 and 90, they’re not going to have a very good experience selecting every single value in that range.
A way to ease the user experience is to create a calculated column featuring a series of discrete values we’ll call “buckets”. These buckets will allow us to group the continuous values into larger buckets that are easier to use as a filter. There are a few different ways we could approach this, but one of the easiest is to create a new column in your query using the Query Editor.
After you open the Query Editor for the table that has the continuous values, go to the Add Column ribbon and click Conditional Column.
With the Conditional Column editor we can specify the column name to use in our condition, the operator, the value for the condition, and the output. Don’t forget to specify the new column’s name and the Otherwise value down at the bottom. The Conditional Column editor allows you to specify values based on different conditions like an IF statement.
And in fact, if you look at the underlying M query, you’ll see that’s exactly what’s happening. We’re just using a GUI to write the IF logic for our conditional column.
When you’re done we have a brand new column called “Grade Buckets” with our buckets. You’ll also notice that in this particular example, I created an additional Conditional Column to use a sort key for the Grade Buckets.
Click Close & Apply to apply your changes to the new query. Set the Sort by Column property for your new buckets field and hide the sort key.
Now we’ve got some much nicer values to use in our slicer that makes life easier for our users!
