Toggle between slicer styles

Power BI
1

Hi all,

I am currently working on a report where the client requested to have the option to either filter results by a specific date range (“between” filter) OR filter by a relative date (“relative date” filter) without having to manually select it in the “between” slicer style.

My initial attempt was to work with bookmarks to toggle between two slicers, but both of them remain active even when they are hidden.

Is there a way I can block a slicer from applying when it is hidden? Is there maybe a completely different technique to achieve this functionality?
The only way I managed to somewhat make it work is by clearing the date filter as part of the bookmark, but that means that the client needs to make the date selection again every time they toggle between the two. I’d like to avoid that.

I have attached an example file where I show the current functionality.

Any help is appreciated.
Thanks,
Fabian

example file for slicer toggle.pbix (91.0 KB)

2

Hi Fabian,

In Power BI, slicers remain active even when they are hidden, which can be an issue in some scenarios like yours. A possible workaround to achieve the desired functionality is to use the disconnected table technique along with the SWITCH function in DAX.

  1. Create a disconnected table with two options - ‘Between’ and ‘Relative’:
  • Go to the ‘Modeling’ tab and click on ‘New Table.’
  • Enter the following formula to create a table:
DateFilterOptions = DATATABLE (
    "Option", STRING,
    {
        { "Between" },
        { "Relative" }
    }
)
  1. Add a slicer to your report based on the ‘Option’ column from the ‘DateFilterOptions’ table. Set the slicer to ‘Single Select.’
  2. Create two slicers for the date range: one for the ‘Between’ date range and another for the ‘Relative’ date range.
  3. Create a measure that dynamically filters the data based on the selected option in the ‘DateFilterOptions’ slicer:
FilteredData =
VAR SelectedOption = SELECTEDVALUE ( DateFilterOptions[Option] )
VAR BetweenFilter =
    CALCULATE (
        [YourBaseMeasure],
        KEEPFILTERS ( DataTable[Date] >= MIN ( BetweenSlicer[Date] ) ),
        KEEPFILTERS ( DataTable[Date] <= MAX ( BetweenSlicer[Date] ) )
    )
VAR RelativeFilter =
    CALCULATE (
        [YourBaseMeasure],
        KEEPFILTERS ( DataTable[Date] >= MIN ( RelativeSlicer[Date] ) ),
        KEEPFILTERS ( DataTable[Date] <= MAX ( RelativeSlicer[Date] ) )
    )
RETURN
    SWITCH (
        SelectedOption,
        "Between", BetweenFilter,
        "Relative", RelativeFilter,
        [YourBaseMeasure]
    )
  1. Use this ‘FilteredData’ measure in your visualizations instead of the base measure.

Now, when users switch between ‘Between’ and ‘Relative’ options in the ‘DateFilterOptions’ slicer, the data in the visualizations will update accordingly based on the selected date range in the corresponding slicer.

Please note that this is a workaround, and it might not be as user-friendly as having slicers that can be toggled on and off, but it should help you achieve the desired functionality.

3

Hi Alisson,
thank you for your detailed response.
Unfortunately, I could not get it working yet.

The BetweenSlicer[Date] and RelativeSlicer[Date] are confusing me. How do I make them separate? Do I have to create separate date tables for each? Do they need to have active relationships with my current date table?

4

Bumping this post for more visibility from our experts and users.

5

I remember seeing something posted some time ago whereby you can ‘switch out’ visuals using the ‘buttons and bookmark’ approach. There is quite a bit there but you should hopefully find something that can help you.

6

Hi @fabian.pohlmeyer

Due to inactivity, we’d like to conclude that your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!