topic Re: Dateslicer controlled by a measure filter in DAX Commands and Tips

Dateslicer controlled by a measure filter

REGHnoob — Thu, 08 Apr 2021 07:35:19 GMT

Hi All

Hope someone can help. This Is bugging the heck out of me. Examble images at the end of the post.

In order to enhance the UI I am trying to create a filter where from where the user can specify set intervals of the past 35 days, the past 60 days and the past 420 days (these are just exambles).

When the filter is selected the date slicer on the page must be filtered accordingly.

So if no filter is selected the entire period is available and if for instance 'offset -35' is selected the date slicer is limited to days tat are no less than 35 days ago as well.

I figured I would add a binary filter to my date slicer – 1 or 0 based and a measure that produces 1 if the value is larger than the specified offset number i.e. -35 else 0. See Image1.

Below I have a measure – DateFilter – based on a disconnected table (image2). It outputs either 1 or 0 relative to the offset value in the Dimension date table.

This seemed to work when I tested in a table and when I set is up as a filter LIST. When I select -35 DateFilter returns 1 for the desired period and 0 before. See image3 and Image4.

But when set as a slicer it doesn’t work. The slicer is can't be modified and just shows dd-mm-yyyy. See image5.

I am guessing it’s because the slicer doesn’t have a selected value. Any Ideas as to how I solve this?

A big Thank You in advance.

Best regards

Kasper

Datefilter =
VAR SelectDaysOffset =
    SELECTEDVALUE ( D_Periodfilter[DaysOffset] )
VAR Datefilter =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( D_Datotable[OffsetDate] )> SelectDaysOffset, 1,
        0
    )
RETURN
    Datefilter

Image1

Image2

Image3

Image4

Image5

Re: Dateslicer controlled by a measure filter

amitchandak — Thu, 08 Apr 2021 03:21:37 GMT

REGHnoob , Based on what I got, an independent date table should help you

refer my video: https://youtu.be/44fGGmg9fHI

Re: Dateslicer controlled by a measure filter

REGHnoob — Thu, 08 Apr 2021 06:52:18 GMT

HI amitchandak

Thank you for the suggestion. It's pretty much the method I have used. I do have an independent table - I just referred to it as disconnected table ( D_Periodfilter ).

But also the suggested solution is not shown to effect the dateslicer it self. It 'only* effects the value table.

The thing that really puzzles me - and I don't think I explained that well enoguh - is the fact that it works as intended when it is set up in a table or a slicer list. But not as a date slicer set up with a slider and between dates.

As seen on images 3 and 4 I only have values for the specified dates - in this case 1. Other than specified dates have the value 0 (Zero). So my solution basically mirros the example you linked to - I just don't have a net value but a fixed value of either 1 or 0 (zero).

Re: Dateslicer controlled by a measure filter

REGHnoob — Thu, 08 Apr 2021 07:36:40 GMT

I think I have managed to add a link to a dummy report to better showcase the issue - inserted at the beginning of the original post.