Slicer Select all option while excluding one value

[dokat]

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 to combine "Grocery+Drug+Depot"

"Grocery"

'Drug"

"Depot"

"Maintenance"

Is this possible to do with POwer BI?

[bcdobbs · Accepted solution]

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

[bcdobbs · Accepted solution]

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]

@bcdobbs This code works perfectly when i select all. It excludes Maintenance sales from the total. However my challenge is after select all if i click on any other slicer selection, sum includes Maintenance. Its almost like bypassing Filter. I only want to include Maintenance sales when user clicks and selects it. Otherwise i'd like to filter out Maintenance sales. Is this possible to do or force user to uncheck select all before making another slicer selection? Appreciate any help.

[dokat]

@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]

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 · Accepted solution]

@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]

Just a thought are you already filtering the column somewhere else eg in the filter pane?

[dokat]

@bcdobbs I figured out the issue, it was with POS CY [Slicer].  It is working right now. Thanks for your help.

[dokat]

@bcdobbs I double checked it, i am not using any other filters. However i am using if functions within {POS TY] not sure if this is causing the problem. Below is the formula for [POS TY]

POS TY = IF(SELECTEDVALUE('POS CY'[Slicer])={"L52W"},Calculate(SUM('POS'[Values]),'POS CY'[Slicer] IN {"L52W"}),IF(SELECTEDVALUE('POS CY'[Slicer])={"L4W"},Calculate(SUM('POS'[Values]),'POS CY'[Slicer] IN {"L4W"}),IF(SELECTEDVALUE('POS CY'[Slicer])={"L13W"},Calculate(SUM('POS'[Values]),'POS CY'[Slicer] IN {"L13W"}))))

Unfortunately, there is confidential information in the data, hard to share it without exposing it.

[bcdobbs]

I'll try and rewrite that but it will be tomorrow morning (uk time).

[dokat]

@bcdobbs Thanks for your response. I fixed the issue is slicer selection. Code is working perfect. no need to rewrite. Thanks for all your help.

[bcdobbs]

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

[dokat]

@bcdobbs Thanks for your reply. If i exclude maintenance in the measure and select maintenance from slicer it will return blank value. I want to have option to be able to select Maintenance and total without maintenance.

Thanks

[bcdobbs]

No, in the measure there's an IF statement that tests whether there is a filter on the column.

If there is it returns the sum you'd expect. It only excludes when everything is selected.

[dokat]

@bcdobbs Is there anyway to make sure it excludes Maintenance sales unless user clicks on it. The issue i am coming across is  if the user dont uncheck select all and click another slicer option formula breaksdown and includes Maintenance sales.