How to Use Two Date Slicers (Custom + Predefined) That Cooperate Without Conflicting?

[Martin-Edan]

Hi everyone,

I'm working on a Power BI report and need some help designing a user-friendly date filtering experience. Here's what I'm trying to achieve:

Goal:

I want to include two date slicers on the same report page:

1. Custom Date Range Slicer

   • A standard "Between" slicer that allows users to manually select a start and end date.

2. Predefined Date Range Slicer

   • A slicer with options like:
     • Past 7 days
     • Past 30 days
     • Past 90 days

Desired Behavior:

• The two slicers should not conflict with each other or double-filter the visuals.
• Instead, they should cooperate:
  • If the user selects a predefined range (e.g., "Past 30 days"), the custom slicer should reflect that range visually.
  • If the user adjusts the custom slicer manually, the predefined slicer should either reset or reflect that it's now a "Custom" selection.

Questions:

• Has anyone implemented something like this before?
• Is it possible to have one slicer influence the other (even visually)?
• Would a disconnected table and DAX measures be the best approach here?

Any guidance, examples, or links to similar solutions would be greatly appreciated!

Thanks in advance,
Richard

[v-priyankata]

Hi @Martin-Edan 
@DataNinja777 Thanks for your inputs.

I hope the information provided by @DataNinja777 was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @Martin-Edan 

I wanted to check if you had the opportunity to review the information provided by user. Please feel free to contact us if you have any further questions.

[DataNinja777 · Accepted solution]

Hi @Martin-Edan ,

Yes, you can absolutely create two cooperating date slicers in Power BI. The best way to do this is by using a disconnected table for your predefined ranges and a central DAX measure to control the filtering logic. This setup prevents the slicers from conflicting with each other and creates the cooperative behavior you're looking for. The core idea is that only one slicer's logic is "active" at a time, determined by the user's selection in the predefined slicer.

First, you'll need to create a new, disconnected table for your predefined options. You can do this by going to the Home tab and selecting Enter data. Create a table named DateRanges with columns like ID, Range, and Days (e.g., 1, "Custom", 0; 2, "Past 7 Days", 7; etc.). It's crucial that this table has no relationships with any other tables in your model. On your report canvas, add a slicer for the DateRanges[Range] field and a second 'Between' style slicer for your main Calendar[Date] field.

The magic happens in a single DAX measure that houses all the filtering logic. This measure checks which option is selected in the predefined slicer and then applies the correct date range to your visuals. It will return a 1 for any date that should be visible and a 0 otherwise.

_DateFilter =
VAR SelectedRange =
    SELECTEDVALUE ( 'DateRanges'[Range], "Custom" ) // Default to "Custom" if nothing is selected
VAR TodayDate =
    TODAY ()
VAR MinCustomDate =
    MIN ( 'Calendar'[Date] ) // The start date selected in the custom slicer
VAR MaxCustomDate =
    MAX ( 'Calendar'[Date] ) // The end date selected in the custom slicer
VAR CurrentDateInVisual =
    MAX ( 'Calendar'[Date] ) // The date being evaluated in the visual's context
VAR IsDateVisible =
    SWITCH (
        TRUE (),
        SelectedRange = "Past 7 Days",
            CurrentDateInVisual > ( TodayDate - 7 ) && CurrentDateInVisual <= TodayDate,
        SelectedRange = "Past 30 Days",
            CurrentDateInVisual > ( TodayDate - 30 ) && CurrentDateInVisual <= TodayDate,
        SelectedRange = "Past 90 Days",
            CurrentDateInVisual > ( TodayDate - 90 ) && CurrentDateInVisual <= TodayDate,
        // If "Custom" is selected, use the date slicer's range
        SelectedRange = "Custom",
            CurrentDateInVisual >= MinCustomDate && CurrentDateInVisual <= MaxCustomDate
    )
RETURN
    IF ( IsDateVisible, 1, 0 )

To make this measure work, select a visual, go to the Filters pane, and drag the _DateFilter measure into the Filters on this visual well. Set its condition to is 1 and apply the filter. You must repeat this for every visual on the page that needs to be filtered by these slicers.

To improve the user experience, you can visually "disable" the custom slicer when it's not in use. Place a rectangle shape over the custom date slicer. Then, create a second DAX measure to control the rectangle's fill color, making it transparent only when "Custom" is selected in the predefined slicer.

_OverlayColor =
IF (
    SELECTEDVALUE ( 'DateRanges'[Range], "Custom" ) = "Custom",
    "#FFFFFF00", // Fully Transparent
    "#F0F0F080"  // Semi-transparent gray to "disable" the view
)

Finally, select the rectangle shape and go to its format settings. Under Style > Fill > Color, click the fx button for conditional formatting. Set the format style to Field value and choose your _OverlayColor measure. Now, the custom slicer will appear grayed out when a predefined range is active and will become fully interactive when the user selects "Custom".

Best regards,

[Martin-Edan]

Thank you for your efforts. This looks very promising and will make an attempt at implementation soon. I had tried something slightly similar and your effort provides more details I was lacking. Thank you very much for your time!