Disable/Enable Multiple Date Slicers, Maybe with Buttons

[x2kibail]

Hey guys,

I've been tasked with creating a report that requires multiple date slicers. These date slicers will need to be 'between slicers'.

I've been asked to include a way to disable/nullify each, so that, for example, a particular end-user wants to use the report while filtering based off one of the three date slicers and doesn't want the other two to affect the report. Or maybe they only want to exclude one of the three slicers. Whatever the solution, it'll need variability 

Best thing would be to have a check box/button for each date slicer that when checked, would disable the slicer. 

Any ideas on how to accomplish this?

[Anonymous · Accepted solution]

Hi @x2kibail 

Perhaps the following methods can help.

My sample:

1. Create three calculated tables as follow

TARGSTARTDATE = VALUES('Table'[TARGSTARTDATE])

COMPLIANCEDATE = VALUES('Table'[COMPLIANCEDATE])

LASTCOMPLETIONDATE = VALUES('Table'[LASTCOMPLETIONDATE])

Use these three tables as slicer

2. Create a measure as follow

Measure = 
VAR _t = IF( SELECTEDVALUE('Table'[TARGSTARTDATE]) >= MIN(TARGSTARTDATE[TARGSTARTDATE]) && SELECTEDVALUE('Table'[TARGSTARTDATE]) <= MAX(TARGSTARTDATE[TARGSTARTDATE]), 1, 0)
VAR _c = IF( SELECTEDVALUE('Table'[COMPLIANCEDATE]) >= MIN(COMPLIANCEDATE[COMPLIANCEDATE]) && SELECTEDVALUE('Table'[COMPLIANCEDATE]) <= MAX(COMPLIANCEDATE[COMPLIANCEDATE]), 1, 0)
VAR _l = IF( SELECTEDVALUE('Table'[LASTCOMPLETIONDATE]) >= MIN(LASTCOMPLETIONDATE[LASTCOMPLETIONDATE]) && SELECTEDVALUE('Table'[LASTCOMPLETIONDATE]) <= MAX(LASTCOMPLETIONDATE[LASTCOMPLETIONDATE]), 1, 0)
RETURN
IF(ISFILTERED(TARGSTARTDATE), _t, IF(ISFILTERED(COMPLIANCEDATE), _c, IF(ISFILTERED(LASTCOMPLETIONDATE), _l, 1)))

3. Put the measure into the visual-level filters, set up show items when the value is 1

This way the three slicers can work separately. If you want one slicer to work, click on the option shown here for the other two slicers.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[x2kibail · Accepted solution]

Thank you all for the help! I do believe creating the three tables, using the measure and the clear selections option on the slicers would do the trick, but it doesn't quite give me everything I want. It's just me being picky, but I really dont want to have to have my users, using the clear selections options on the slicers. 

I guess I was being impatient before, because with a bit more digging, I was able to find the exact solution I wanted. The key is the option to use 'selected visuals' when setting up bookmarks. Please see below for a walkthrough of what I did:

1. Set up report, how you would like to see it. (including buttons, there should be two buttons for each date slicer)

2. For each date slicer, create a bookmark for when the slicer be 'disabled'.

    a. use clear selections, to make sure all the data is reset to default

    b. add two shapes and cover date boxes, so they cannot be selected.

    c. in the selection pane, hide the button you will use for disabling the slicer (pretend the picture says Disable) 

    d. selecting objects/slicers you want affected while creating the bookmark

    e. set the bookmark to work on 'selected visuals' not 'all visuals'

3. set button for disabling slicer

   a. set button with action to deploy disable bookmark

   b. when selected, the slicer should clear its selections, the shapes should appear over the date boxes, and the button should disappear (we will set the second button to take its place)

4. For each date slicer, create a bookmark for when the slicer will be 'enabled'.

    a. in selection pane, hide shapes that cover the slicer date boxes and hide the button you will use for enabling

    b. do this by selecting objects/slicers you want affected while creating the bookmark

    c. set the bookmark to work on 'selected visuals' not 'all visuals'

5. set button for enabling the slicer

   a. set button with action to deploy enable bookmark

   b. when selected the slicer should have no impediments from being used and you should not see the 'enabler button' but the 'disable button'

6. repeat steps 2-6 with each date slicer

[Anonymous · Accepted solution]

Hi @x2kibail 

Perhaps the following methods can help.

My sample:

1. Create three calculated tables as follow

TARGSTARTDATE = VALUES('Table'[TARGSTARTDATE])

COMPLIANCEDATE = VALUES('Table'[COMPLIANCEDATE])

LASTCOMPLETIONDATE = VALUES('Table'[LASTCOMPLETIONDATE])

Use these three tables as slicer

2. Create a measure as follow

Measure = 
VAR _t = IF( SELECTEDVALUE('Table'[TARGSTARTDATE]) >= MIN(TARGSTARTDATE[TARGSTARTDATE]) && SELECTEDVALUE('Table'[TARGSTARTDATE]) <= MAX(TARGSTARTDATE[TARGSTARTDATE]), 1, 0)
VAR _c = IF( SELECTEDVALUE('Table'[COMPLIANCEDATE]) >= MIN(COMPLIANCEDATE[COMPLIANCEDATE]) && SELECTEDVALUE('Table'[COMPLIANCEDATE]) <= MAX(COMPLIANCEDATE[COMPLIANCEDATE]), 1, 0)
VAR _l = IF( SELECTEDVALUE('Table'[LASTCOMPLETIONDATE]) >= MIN(LASTCOMPLETIONDATE[LASTCOMPLETIONDATE]) && SELECTEDVALUE('Table'[LASTCOMPLETIONDATE]) <= MAX(LASTCOMPLETIONDATE[LASTCOMPLETIONDATE]), 1, 0)
RETURN
IF(ISFILTERED(TARGSTARTDATE), _t, IF(ISFILTERED(COMPLIANCEDATE), _c, IF(ISFILTERED(LASTCOMPLETIONDATE), _l, 1)))

3. Put the measure into the visual-level filters, set up show items when the value is 1

This way the three slicers can work separately. If you want one slicer to work, click on the option shown here for the other two slicers.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ankeet1pi9]

You will need three different date tables, one for each slicer. Set the slicers' settings to use BETWEEN.

 (Image Source: learn.microsoft.com)

[x2kibail]

Here is what I built out so far (with some data blacked out), so you guys have a picture of what I'm trying to get at.

[sayaliredij]

Hi @x2kibail 

Not sure how your report look like

But in case you can share report with sample data in it, i can check the same 

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[x2kibail]

is this enough to give you an idea?

[sayaliredij]

HI @x2kibail 

I believe calculation groups can solve your problem 

Please refer the article below that exactly solves the problem you are facing

https://www.sqlbi.com/articles/using-calculation-groups-to-switch-between-dates/

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[x2kibail]

I'll need to look deeper, but through a quick glance, I don't think this accomplishes exactly what I need. 

I need the date slicers to be in the between format. I need to see data in a table, mostly raw data. I'm not dealing with any aggregations ...