Forum Discussion

FloBo's avatar
FloBo
New Member
5 years ago
Solved

Using slicer under condition

Hi, I don't know if the title is really clear.

Here is my problem:
I have a report with pre-recorded date ranges that the user can select using a "Chiclet Slicer". Basically He can choose between 3 options : "W", "M" and "Y" (for Week, Month and Year) and I have 2 measures called StartDate and EndDate that use a combination of SWITCH and SELECTEDVALUE to determine what range to use.

e.g. if the users selects "M" then SartDate returns DATE( YEAR( TODAY() ); MONTH( TODAY() ); 1 ) and EndDate returns EOMONTH( TODAY() ).

Then I use those measures to calculate every others (DATESBETWEEN( Dates, StartDate, EndDate).

And it works fine.

But my users would like to add a 4th option to W/M/Y: they would like to have the possibility to select any date they want.

So I added a 4th chiclet called "C" (for Custom) and a classic date slicer where you can pick a start date and an end date. I also added this possibility in my SWITCH : MIN( Dates[Date] ) and MAX( Dates[Date] ).

This works pretty fine with all visuals that don't have a date axis. But when I put my measure on a column chart with dates on x axis I have a problem :
for example let say that my user picks up "A" (so from 01/01/2021 to today) and that the date slicer is between 02/01/2021 and 02/28/2021.

- If I say that the date slicer must filter the visual, it will only show the data in february, even if "A" is selected. It shows the correct data only if the date range in the slicer is larger than the range defined by the chiclet.

- If I say that the date slicer must NOT filter the visual it shows data properly when I pick up W/M/Y (because dates are calculated no mater what) BUT it shows all the data when I pick up "C" (because it doesn't care what is in the date slicer).

 

My question is: is it possible to do something like that? And if yes, how can I do?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi FloBo 

    I think the you can try to build a filter measure, add this measure into visual filter and set it to show items when value = 1, when you want to get correct result in visuals with date axis.

    My sample:

    Table:

    Build an unrelated date table.

    Date = CALENDARAUTO()

    My measure:

    Filter =
VAR _Select =
    SELECTEDVALUE ( Chiclet[Chiclet] )
VAR _StartDate =
    SWITCH (
        TRUE (),
        _Select = "W",
            TODAY () - WEEKDAY ( TODAY (), 2 ) + 1,
        _Select = "M", DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
        _Select = "Y", DATE ( YEAR ( TODAY () ), 01, 01 ),
        _Select = "C", MIN ( 'Date'[Date] )
    )
VAR _EndDate =
    SWITCH (
        TRUE (),
        _Select = "W",
            TODAY () + 7
                - WEEKDAY ( TODAY (), 2 ),
        _Select = "M", EOMONTH ( TODAY (), 0 ),
        _Select = "Y", DATE ( YEAR ( TODAY () ), 12, 31 ),
        _Select = "C", MAX ( 'Date'[Date] )
    )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= _StartDate
            && MAX ( 'Table'[Date] ) <= _EndDate,
        1,
        0
    )

     Result is as below.

    You can download the pbix file from this link: File

     

    Best Regards,

    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi FloBo 

    I think the you can try to build a filter measure, add this measure into visual filter and set it to show items when value = 1, when you want to get correct result in visuals with date axis.

    My sample:

    Table:

    Build an unrelated date table.

    Date = CALENDARAUTO()

    My measure:

    Filter =
VAR _Select =
    SELECTEDVALUE ( Chiclet[Chiclet] )
VAR _StartDate =
    SWITCH (
        TRUE (),
        _Select = "W",
            TODAY () - WEEKDAY ( TODAY (), 2 ) + 1,
        _Select = "M", DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
        _Select = "Y", DATE ( YEAR ( TODAY () ), 01, 01 ),
        _Select = "C", MIN ( 'Date'[Date] )
    )
VAR _EndDate =
    SWITCH (
        TRUE (),
        _Select = "W",
            TODAY () + 7
                - WEEKDAY ( TODAY (), 2 ),
        _Select = "M", EOMONTH ( TODAY (), 0 ),
        _Select = "Y", DATE ( YEAR ( TODAY () ), 12, 31 ),
        _Select = "C", MAX ( 'Date'[Date] )
    )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= _StartDate
            && MAX ( 'Table'[Date] ) <= _EndDate,
        1,
        0
    )

     Result is as below.

    You can download the pbix file from this link: File

     

    Best Regards,

    Rico Zhou

     

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

  • FloBo's avatar
    FloBo
    New Member
    5 years ago

    anks a lot Anonymous !

    Building an unrelated date table works perfectly!