Forum Discussion

pstp's avatar
pstp
Frequent Visitor
5 years ago
Solved

Include/Exclude Slicer

I have a dataset and report showing data for weekly events. My date dimension table has a column designating week type for each date. There are only three options: normal, Christmas, and Easter. I wo...
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi pstp ,

     

    Try this:

     

    1. Create a slicer table without any relationships with other tables.

    Slicer Table:

    SlicerValue
    Include Christmas and Easter
    Exclude Christmas and Easter

     

    2. Create a measure.

    Measure =
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[SlicerValue] ),
    "Include Christmas and Easter", COUNTROWS ( Dates ),
    "Exclude Christmas and Easter",
        CALCULATE (
            COUNTROWS ( Dates ),
            FILTER (
                ALLSELECTED ( Dates ),
                Dates[WeekType] = MAX ( Dates[WeekType] )
                    && Dates[WeekType] = "Normal"
            )
        )
)

     

    3. Use the measure as "filters on this visual".

     

    4. Test.

     

     

    Best Regards,

    Icey

     

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

Icey's avatar
Icey
Community Support
5 years ago

Hi pstp ,

 

Try this:

 

1. Create a slicer table without any relationships with other tables.

Slicer Table:

SlicerValue
Include Christmas and Easter
Exclude Christmas and Easter

 

2. Create a measure.

Measure =
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[SlicerValue] ),
    "Include Christmas and Easter", COUNTROWS ( Dates ),
    "Exclude Christmas and Easter",
        CALCULATE (
            COUNTROWS ( Dates ),
            FILTER (
                ALLSELECTED ( Dates ),
                Dates[WeekType] = MAX ( Dates[WeekType] )
                    && Dates[WeekType] = "Normal"
            )
        )
)

 

3. Use the measure as "filters on this visual".

 

4. Test.

 

 

Best Regards,

Icey

 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

This is Awesome. I am trying to do something similar.. I have a dataset with partners and their activities essentialy. I want the same idea, where you have a slicer when selected it shows all the partners except a certain partner... Lets say " PartnerName" and when it is not selected it shows all the partners. I was hoping to add it to a few different types of visuals in power bi.