Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
1 year ago
Solved

Date slicer combining "after" and "between"

Hi folks,


I'm using a date slicer with "after" so that it's always the "last date".

However, some people who work with the report also need to set a specific time period ewhich usually works with "between".

 

Since I can only set one of those two options in one slicer, I was wondering how to best solve this issue.

 

Here are some possibilites:

 

  • I could use 2 data slicers.
  • I could work with bookmarks.
  • Probabaly could do something with DAX?

(I don't have a date table in this report, only a columns with invoice dates)

 

However, I wanted to know if you have any best practice on this?

 

Thx in advance! đź™‚

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi cn4422 

     

    Unfortunately using two slicers to filter on the same field is not supported currently.

    But maybe you can try this alternative workaround:

    Here's the sample table:

    Table:

    Then add 2 calculated tables:

     

     

    Between = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
    After = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

     

    Make sure that there isn't any relationship between the 3 tables:

    Add 2 Slicers with the fields After[Date] and Between[Date]:

    Create a measure:

     

     

    MEASURE =
VAR _between_min =
    MIN ( 'Between'[Date] )
VAR _between_max =
    MAX ( 'Between'[Date] )
VAR _after =
    MIN ( 'After'[Date] )
RETURN
    IF (
        (
            SELECTEDVALUE ( 'Table'[Date] ) >= _between_min
                && SELECTEDVALUE ( 'Table'[Date] ) <= _between_max
        )
            || SELECTEDVALUE ( 'Table'[Date] ) >= _after,
        1
    )

     

     

    Then drag it to the filter of the target visuals and set it equal to 1:

     

    The result is as follow:

     

    Best Regards

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cn4422 

     

    Unfortunately using two slicers to filter on the same field is not supported currently.

    But maybe you can try this alternative workaround:

    Here's the sample table:

    Table:

    Then add 2 calculated tables:

     

     

    Between = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
    After = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

     

    Make sure that there isn't any relationship between the 3 tables:

    Add 2 Slicers with the fields After[Date] and Between[Date]:

    Create a measure:

     

     

    MEASURE =
VAR _between_min =
    MIN ( 'Between'[Date] )
VAR _between_max =
    MAX ( 'Between'[Date] )
VAR _after =
    MIN ( 'After'[Date] )
RETURN
    IF (
        (
            SELECTEDVALUE ( 'Table'[Date] ) >= _between_min
                && SELECTEDVALUE ( 'Table'[Date] ) <= _between_max
        )
            || SELECTEDVALUE ( 'Table'[Date] ) >= _after,
        1
    )

     

     

    Then drag it to the filter of the target visuals and set it equal to 1:

     

    The result is as follow:

     

    Best Regards

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

     

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    Hi cn4422 ,

    Please try below steps:

    1. Add a Date Table: Create a dedicated Date table to improve filtering flexibility and support both options.
    2. Two Slicers with Bookmarks:
    - Create one slicer with 'After' and another with 'Between'.
    - Use bookmarks to toggle between them, allowing users to choose their preferred filter method.
    3. DAX Measure Option:
    - Create a DAX measure to dynamically detect the chosen filter and adjust the report’s data accordingly.

    This approach offers flexibility while keeping the report user-friendly. Let me know if you need detailed steps for implementation!

     

    Please mark this as solution if it helps you. Appreciate Kudos.

    • cn4422's avatar
      cn4422
      Icon for Helper V rankHelper V
      1 year ago

      Hi FarhanJeelani thanks for your reply!

       

      3. DAX Measure Option:
      - Create a DAX measure to dynamically detect the chosen filter and adjust the report’s data accordingly.

      This approach offers flexibility while keeping the report user-friendly. Let me know if you need detailed steps for implementation!

      I've already implemented the 2 sliders with toggle-function (bookmarks).

       

      But I didn't manage to create the DAX measure to dynamically detect the chosen filter...

       

      Do you have an example for this?