Include/Exclude Slicer

[pstp]

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 would like to set up a filter that simply allows the user to include or exclude data from Christmas and Easter weeks. Is there an easy way to achieve this? Thanks!

[Icey · Accepted solution]

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]

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. 

[Fowmy]

@pstp 

You can insert a Slicer on the report and add the week type column which will allow the user to filter as needed.

[pstp]

@Fowmy That is what I currently have. It has been asked that it change to a single select, where the two options are Include or Exclude. I don't know how to ensure "Normal" weeks are included in both options.

[Fowmy]

@pstp 

You can either change the slicer selection option  to allow multi-select or right click on the week type filed and group it in to two categories Normal and Other Two