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

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,