Numeric data lends itself to ranges in visualizations. End users might want to see sales for a specific time period or show sales that fall above or below a specific benchmark. When this is the case, you can use Microsoft Power BI’s numeric range slicer.
Users will appreciate the flexibility of viewing visualizations that offer this type of insight, which they can apply themselves if they are properly trained to use the feature. Even then, it’s a bit messy.
In this tutorial, I’ll show you how to add a numeric range slicer to a Power BI visualization and discuss the pitfalls this type of slicer will present. What you’ll learn is that without proper training, users might get more than a bit lost. Unfortunately, this slicer doesn’t work with measures.
SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)
I’m using Microsoft Power BI Desktop on a Windows 10 64-bit system. You can also use Power BI Service. If you’re using Microsoft Power Bi Service, note that you can decide whether users can view a published report in Edit mode, which will affect their ability to apply slicer types. If you disable edit mode, don’t include numeric range slicers.
You can download the Microsoft Power BI demo file for this tutorial.
How to plan the slicer in Power BI
When using a Power BI numeric range slicer, users have four options:
- Values between two specified numbers
- Values less than a specified number
- Values greater than a specified number
- Values relative to a specified time or date
Figure A shows a simple table matrix with two numeric columns based on the Sales table. This is where knowing your data matters. The dates denote sales for 2021 and 2022. Users might want to see values from week to week, month to month, quarter to quarter or even year to year. Giving them the choice is an easy step.
Let’s begin by adding a numeric range slicer for the dates.
How to add a numeric range slicer in Power BI
Users are sure to appreciate the ability to see sales for specific time periods. Add the slicer by clicking Slicer in the Visualization pane. Then, add the SalesDate column from the Sales table to the Field bucket (Figure B).
As you can see, the slicer defaults to the first and last date in the SalesDate field. Your work as designer is done, sort of. Without the proper training, users won’t know how to use this slicer to its full advantage.
How to use the handles in numeric range slicer in Power BI
At this point, the slicer is ready to use if all that’s used are the two handles. By default, the slicer can display every date. The left handle displays the oldest date, and the right handle displays the most recent date.
By moving either handle, you filter the underlying records in the matrix visualization. For example, Figure C shows the right handle moved to 12/31/2021 of the published report. Consequently, the matrix displays records for 2021. When you’re done, click the Clear Selections button in the top-right of the slicer.
Similarly, you can move the left handle to display records for 2022 (Figure D). Be sure to return the right handle to the far right first. Think of the handles as the first and last dates.
If you have trouble landing on a specific date, hold down the Ctrl key while pressing the right or left arrow key. Doing so will nudge the handle a bit. That nudge should equal a single day. Another way to quickly choose a specific date is to click the Date Picker beside each date. These lose their usefulness when the time period covers several months or even years.
How to use the slicer type options in Power BI
The handles are handy, and knowing how to nudge the handles makes them even easier to use. However, sometimes you’ll want a bit more precision.
When designing the report, the slicer type dropdown is available by default in the slicer’s header to the right of the name. If you don’t see it, click Format in the Visualization pane and turn on the slicer’s header. It’s not readily visible in a published report, which must be an oversight because users won’t know how to display it. Perhaps this will change in a future update.
The user can display the dropdown and use the different slicer types by clicking Edit in the published report’s menu. If you still don’t see it, click Mobile Layout, and then, click Web Layout. Figure E shows the dropdown and types in Edit mode.
The slicer types are self-explanatory, but there are a few things the user will need to know.
Between is the default, so choosing it seems to do nothing, unless you’ve applied another type previously.
Before removes the left handle. Move the right handle to change the upper value of the date range.
After removes the right handle. Move the left handle to change the lower value of the date range.
Relative Date displays options for specifying a date relative to the current date (Figure F).
Relative Time displays options for specifying a time relative to the current time (Figure G).
For the two latter types, the first dropdown displays Last, Next and This. Using the two dropdowns together offers a lot of flexibility. For instance, you might want to see values for the last month or the current (this) hour. Both the date and time will update if you refresh the report. Power BI caches the results of these relative date and time queries. You must refresh the report or risk seeing the same results as before and not realizing it.
Currently, Power BI doesn’t consider time zones. That means you and a colleague in a different time zone will see values evaluated in the UTC, which is the local time at Greenwich, England. This may or may not be helpful, but this is something users should know.
Using slicer types in a published report is rife with pitfalls most users won’t be familiar with. Nor will they know how to avoid them without training or card visualizations dropped in by the designer with specific instructions.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays