# Slicers: delete/disable slicer member?



## ak12

Is there a way to delete/disable/filter dimension members that are shown as slicer buttons?

Since my data set is huge I cannot display the slicer dimension in my pivottable, and apply a filter there.

Thanks in advance.


----------



## AlbertoFerrari

ak12 said:


> Is there a way to delete/disable/filter dimension members that are shown as slicer buttons?
> 
> Since my data set is huge I cannot display the slicer dimension in my pivottable, and apply a filter there.
> 
> Thanks in advance.


 
Can you provide a clearer example? Are you trying to use a sliecr for customer name, for example, and you have one million customers?


----------



## ak12

sliecr for customer name, for example, and you have one million customers?
Yes, this is what I want to do.

I have loaded my PowerPivot with fact from a SSAS cube. In one of the dimensions, say a Product dimension, I have a product account called "Other" that I want to omit from the dimension/slicer.


----------



## AlbertoFerrari

ak12 said:


> sliecr for customer name, for example, and you have one million customers?
> Yes, this is what I want to do.
> 
> I have loaded my PowerPivot with fact from a SSAS cube. In one of the dimensions, say a Product dimension, I have a product account called "Other" that I want to omit from the dimension/slicer.


 
Ok, let's say you have a column called "ProductName" and you want to avoid the ones where the "ProcductCategory" is "Other".
You can create a new calculated column:


		Code:
__


NewProductName = IF (
    Product[ProductCategory] = "Other",
    BLANK (),
    Product[ProductName]
)

And then use a slicer on this column, where all of the products with category "Other" will be collapsed in a single blank value.
Does it help? I strongly suggest everybody to post examples and screenshots to make their request clearer... otherwise it is hard to interpret the needs.
Alberto


----------



## ak12

Alberto, thank you for your commitment to have this sorted out. We are almost down to the core of this.

I am not trying to *avoid* members in the dimension as in your suggested solution. 

I want to *omit/delete/hide* them altogether from the slicer. 

Is it possible to omit/delete/hide the single blank value, making it not visible in the slicer at all?

Maybe a better suited SSAS cube that is a sub set of the original SSAS cube is the best solution?


----------



## AlbertoFerrari

ak12 said:


> Alberto, thank you for your commitment to have this sorted out. We are almost down to the core of this.
> 
> I am not trying to *avoid* members in the dimension as in your suggested solution.
> 
> I want to *omit/delete/hide* them altogether from the slicer.
> 
> Is it possible to omit/delete/hide the single blank value, making it not visible in the slicer at all?
> 
> Maybe a better suited SSAS cube that is a sub set of the original SSAS cube is the best solution?


 
Well, you know, I'm Italian and - to me - the difference between "avoiding" and "omitting" (or "hiding") is far from being clear, they all mean the same to me.
Can you provide a picture or a sample workbook? I simply don't understand.


----------



## ak12

It is all very simple 

It all boils down to this: Can the single blank value button in the slicer be deleted?

Albertos suggestion:
...And then use a slicer on this column, where all of the products with category "Other" will be collapsed in a single blank value....


----------



## AlbertoFerrari

ak12 said:


> It is all very simple
> 
> It all boils down to this: Can the single blank value button in the slicer be deleted?
> 
> Albertos suggestion:
> ...And then use a slicer on this column, where all of the products with category "Other" will be collapsed in a single blank value....


 
Oh, well... now the problem is very clear. BUT I don't have a solution.  
I don't think there is a way to avoid the slicer to show all the values in a column, even if the fact table does not contain any data, all the column values will be shown, AFAIK. BLANK() is a value as any other... maybe some Excel guru will have a better luck...


----------



## ak12

Alberto: I appreciate your commitment to solving this. Thank you.


----------



## powerpivotpro

At Pivotstream, when we want to get rid of that blank value in the slicer, we go back into the db and trim out the rows of data that don't match.

For instance, if we have three customers in our Customers table - A, B, and C.  And then we have rows in our Sales table that either have a CustomerID = D, or a blank CustomerID, we get rid of those rows from Sales (or add customer D to the Customers table, etc.)

That's the only way to truly fix this problem.


----------



## ak12

Is there a way to delete/disable/filter dimension members that are shown as slicer buttons?

Since my data set is huge I cannot display the slicer dimension in my pivottable, and apply a filter there.

Thanks in advance.


----------



## ak12

...we go back into the db and trim out the rows of data that don't match.

How do you trim out rows in PowerPivot?


----------



## powerpivotpro

We don't trim rows out of PowerPivot, sorry.  You have to delete the "bad" rows in the data source, whatever that is.

In our case, we trim them from the original SQL Server database and then refresh the table in PowerPivot.


----------



## ak12

Rob,
Thank you for the answer and your clarification.

As you suggested, I got around the problem by filtering the MDX query in the PowerPivot SSAS wizard.


----------

