Forum Discussion

x2kibail's avatar
x2kibail
Helper I
2 years ago
Solved

Disable/Enable Multiple Date Slicers, Maybe with Buttons

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?

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    x2kibail
    2 years ago

    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

8 Replies

    • x2kibail's avatar
      x2kibail
      Helper I
      2 years ago

      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 ...

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    2 years ago

    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's avatar
    x2kibail
    Helper I
    2 years ago

    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.

  • ankeet1pi9's avatar
    ankeet1pi9
    Regular Visitor
    2 years ago

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

     

     

     (Image Source: learn.microsoft.com)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    x2kibail
    Helper I
    2 years ago

    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