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