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 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!

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.

5 Replies

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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      pstp
      Frequent Visitor
      5 years ago

      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's avatar
        Fowmy
        Super User
        5 years ago

        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