Power BI is a fantastic self-service BI tool and not least because of this Microsoft has expanded its market leadership in the data & analytics area, as also confirmed by Gartner in 2021.
Despite the great features and monthly updates, there is still one basic feature that has not yet made it into Power BI Desktop: The ‘Single Date Picker’. 😧
With Power BI Desktop there are various possibilities to select a date or date range, either as a list (see screenshot 1), dropdown (see screenshot 2), interval (see screenshot 3) or as a hierarchy (see screenshot 4). There are also some custom visuals with corresponding functionality.
Screenshot 1: List via Table Visual
Screenshot 2: Dropdown via Slicer-Visual
Screenshot 3: Interval via Slicer-Visual (Between)
Screenshot 4: Date-Hierarchy via Slicer-Visual
Unfortunately, none of these variants offer the ability to select only a single date value using the Power BI Slicer visual. Corresponding requests from the community under ‘Power BI Ideas’ have not yet been implemented (Single Date Picker in Power BI Desktop, Select a single date from pop-up calendar in date slicer).
The topic has also been covered by Patrick LeBlanc (like Adam Saxton he is a ‘Guy in a Cube’).
Based on Patrick’s videos, here is a modified approach on how to use a ‘Single Date Picker’ in a Power BI report with a little trick. The goal is to use the Power BI standard slicer visual and not to customize or duplicate any existing DAX measures.
Here we go! 🤗
Below are the steps in detail to use a Single Date Picker in Power BI Desktop via the slicer visual.
Step 1: Selecting the slicer visual
Just like in the videos, we use the slicer visual and select the ‘After’ option to make only the start value selectable.
Step 2: TOP N-Filter
In this step, we will now take a different approach than in Patrick’s video to avoid adjusting DAX measures.
In the table visual we add our date as a visual filter and use the filter type ‘Top N’ with ‘Show items’ configured to 1 and with ‘By Value’ we select the date field from the date table, which is then displayed as ‘Earliest Date’.
Furthermore, we set the ‘Slider’ to ‘Off’ in the slicer settings.
Now all values with the selected date are displayed in our table visual.
Step 3: Layout of the Slicer Visual
To hide the end date in our slicer visual, we place a ‘Rectangle’ shape (Insert / Shapes) over it and adjust the frame and background color to the background. Additionally, the slicer visual and the rectangle shape are grouped (select both elements, right click it and select ‘Group > Group’) to simplify handling (e.g., positioning in the report).
Alternatively, you can switch off ‘Responsive’ in the ‘General’ settings of the slicer and adjust the size so that only the first input field is visible.
Step 4: Note for users (optional)
It is important to understand that in the text box it is possible to enter a date that is before the existing data. Our example contains data for 06/12/2021 and 07/12/2021. However, if the user enters a date before 06/12/2021, the data for 06/12/2021 will be filtered due to the ‘After’-setting in the visual.
Since this may be confusing for the user, it is recommended to create the following hint via a DAX measure (if you don’t know DAX yet, here are 10 Reasons why our Power BI Users love DAX).
This can then be displayed below the slicer visual.
Yay, with that we have a ‘Single Date Slicer’ with just a few steps. 😊
This approach has the advantage that the Power BI standard slicer visual is used, and no DAX measures must be adapted. However, as a report creator you should pay attention to set the TOP N filter for all relevant visuals (see step 2 above). In addition, it is important that the date table has no gaps (e.g., missing days). Otherwise, when such a day is selected using the slicer, the next available date in the table visual is selected. If the date table is filled without gaps, this is not the case and the data in the table visual will be filtered correctly.
Of course, this is still a workaround, and it would be desirable if the functionality is integrated directly in the slicer visual. On the other hand, this example shows the flexibility of Power BI and as so often: ‘All roads lead to Rome’. 😉
As a Microsoft Gold Partner with a focus on Data, Analytics & AI we conduct public Power BI workshops with Microsoft, but also directly for companies.
More information can be found here: https://www.obungi.com/workshops-events-training/
Be the first to comment