How to include "Blank" as part of ALL in a slicer.

[TusharGaurav]

Hi Experts,

I have a table as below:

Industry	Revenue
Agriculture	100
Tourism	120
IT	150
Retail	210
	175

I want to create a slicer for "Industry" , where if there is a value "All". i.e. in Slicer I should see the below values:

Agriculture

Tourism

IT

Retail

All

Now if "All" is selected in the slicer ,I should see total revenue i.e. 755.

Can you please suggest how to achieve this.

Thanks and Regards,

Tushar Gaurav

[samratpbi]

Hi, I understand you dont want to show the blank as an option in the slicer, however you want to consider that when select all. To achieve this in simple way is, you can first enable Select All option in Slicer settings.

then in the Filter option, uncheck the blank 

As you can see blank option not available in the slicer however when you select all, it would consider blank

This would have 1 potential drawback: if you first Select All, and then uncheck few items, slicer will still consider blank, however if you select items individually then it should be fine. (I am considering you would need multi-select option)

Hope this helps to resolve your problem, if it does, then please mark it as solution.

Thanks - Samrat

[collinq]

Hi @TusharGaurav ,

@pbiuseruk answer is the proper way to do this but if you really, really want to build your own you could do a custom column in M or Dax that includes all the other options.  This would be much more difficult than it is worth and in the end it would be the "select all" option.

[pbiuseruk]

How is this possible - just curious?

When I was having a think about this, you could technically make a bunch of separate measures, each for a specific option within the drop down and then make a parameter field slicer instead. That would work but it's just such an overkill. I'd recommend this approach if there was a different use case - e.g. you wanted to group 2 categories and also allow them to be individually selected within one slicer. For selecting "All", it's not really worth the extra maintenance headache as the out of the box functionality is more than sufficient.

If someone decides to go with my field parameter approach - I'd highly recommend to make a base measure first and then just make the subsequent measures, reference the base measure and apply the additional filtering. 

[pbiuseruk]

There's a select All option for slicers. You'd need to use that.

The problem you'd face with trying to make an actual "All" category is that the slicer is tied to a column with values. Each row would have one value and not 2. E.g. Tourism would either have to be "Tourism" or "All" - it can't be both. Much better off just using the select all option though but just added the description for your awareness.

Hope that helps.