Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

Slicer Select all option while excluding one value

Hi, I am trying to create a slicer and have select all option but want to exclude one value from the selectall.   Here are the four values for the sicer.  I'd like to have select all options only ...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    You can't make a slicer default to only certain values when you click Select All.

    However you could build the logic into a measure:

     

    Sum Of Amount (Special Slicer) = 
    IF ( 
        ISFILTERED ( Sales[Item Type] ),
        SUM ( Sales[Amount] ),
        CALCULATE ( 
            SUM (Sales[Amount] ),
            Sales[Item Type] <> "Maintenance"
        )
    )


    Example File

  • dokat's avatar
    dokat
    4 years ago

    bcdobbs I just downloaded your file and it's working however when i applied to my report it is not. Logic is solid and makes sense not sure why but it is not working on my end.

    Here is the code i am using.

    Special Slicer TY = 
    IF ( 
        ISFILTERED ( 'POS CY'[Slicer]),
         [POS TY],
        CALCULATE ( 
            [POS TY],
            'POS CY'[Slicer] <> "Maintenance"
        )
    )
bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

You can't make a slicer default to only certain values when you click Select All.

However you could build the logic into a measure:

 

Sum Of Amount (Special Slicer) = 
    IF ( 
        ISFILTERED ( Sales[Item Type] ),
        SUM ( Sales[Amount] ),
        CALCULATE ( 
            SUM (Sales[Amount] ),
            Sales[Item Type] <> "Maintenance"
        )
    )


Example File

dokat's avatar
dokat
Post Prodigy
4 years ago

bcdobbs  Thanks for your response. I just tried your code however when click on select all option in the slicer it didnt exclude the values for "Maintenance". Filter didnt work and include sum for all values.

 

Thanks

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Hi,

    Have you seen the sample file I sent: Example File

    As far as I can see that code is working in there:

     

    Selecting all excludes the two 4's:

    Selecting "Maintencance" returns 8:

     

    Entirely possible I'm missing the point of what you're trying to achieve; if so appologies.

     

    • dokat's avatar
      dokat
      Post Prodigy
      4 years ago

      bcdobbs I just downloaded your file and it's working however when i applied to my report it is not. Logic is solid and makes sense not sure why but it is not working on my end.

      Here is the code i am using.

      Special Slicer TY = 
    IF ( 
        ISFILTERED ( 'POS CY'[Slicer]),
         [POS TY],
        CALCULATE ( 
            [POS TY],
            'POS CY'[Slicer] <> "Maintenance"
        )
    )
      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Are you able to share a version of your report even if you cut it down?