Forum Discussion
Using "Between" Slicer to Only Select Dates in Data Field
Hi,
My goal is to have a "Between" style slicer that allows the user to only select a max and min date from the available dates in the table. So for example, I could have data for 7 month-ends (31-Oct-22, 31-Dec-22, and so on as shown) stored in a DateTime field but if I choose to use the "Between" style slicer, the user can select any date by sliding the controls (in this example, randomly 16-Feb-23 and 23-Aug-23). How can I restrict the slider to only show the 7 available dates as allowable options as you drag across? The poor alternative is to have a "Vertical List" style slicer and allow multiple selections. This is far from perfect because I have visuals that are showing data calculated as differences between two dates and the user can select any number of dates in the "Vertical List". The "Between" style slicer is the obvious choice.
Thanks,
Darren.
Hi,
This functionality is not currently available in the between slicer, you may be able to find a custom visual to help with what you need but I can't say I've seen one. The vertical list is currently your best solution for this without changes to the data model, as you have mentioned it's not ideal.
An alternative solution could be to create sepearte fields for the start and end date and use drop downs with single select. This would depend on your data and size of the data as you may need to expand the records to populate the dates between and use a joining table in the data model.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
3 Replies
- 123abc
Community Champion
If you want to restrict the "Between" style slicer to only allow selection of specific dates from your available data, you may need to use a workaround. Unfortunately, as of my last knowledge update in January 2022, Power BI does not provide a direct way to limit the range of dates in the "Between" slicer to only those present in your data.
However, you can achieve a similar effect using the following approach:
Create a Date Table: Ensure that you have a separate Date table in your Power BI model that includes all the relevant dates in your dataset.
Link Date Table to Your Data Table: Establish a relationship between the Date field in your data table and the Date table.
Use Date Table in Slicer: Instead of using the Date field from your data table directly in the "Between" slicer, use the corresponding Date field from the Date table. This way, the slicer will only show dates available in the Date table.
Apply a Measure for Filtering: Create a measure that checks if the selected date is within the range of your available dates. For example:
IsDateInRange =
IF (
MIN('DateTable'[Date]) <= MIN('DataTable'[YourDateField]) &&
MAX('DateTable'[Date]) >= MAX('DataTable'[YourDateField]),
1,
0
)This measure returns 1 if the selected date is within the range of your data and 0 otherwise.
Use Measure for Visuals: When creating your visuals, use the IsDateInRange measure as a filter to ensure that only the selected dates within your desired range are considered.
This way, even though the "Between" slicer may show a broader range of dates, the visuals will be filtered based on the availability of dates in your data.
Please note that the specific steps and DAX measures might need adjustment based on your exact data model and requirements. Additionally, Power BI features may have evolved since my last update, so it's a good idea to check for any new developments in Power BI functionality.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hi SS,
Thanks for your swift reply. Indeed I am using such a workaround for another reason. This query was specifically about the functioning of the "Between" slicer. It doesn't appear that what I am asking is available, at least not as of this date.
- DOLEARY85
Resident Rockstar
Hi,
This functionality is not currently available in the between slicer, you may be able to find a custom visual to help with what you need but I can't say I've seen one. The vertical list is currently your best solution for this without changes to the data model, as you have mentioned it's not ideal.
An alternative solution could be to create sepearte fields for the start and end date and use drop downs with single select. This would depend on your data and size of the data as you may need to expand the records to populate the dates between and use a joining table in the data model.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
