# Keeping Blank Columns when applying a slicer to a table from PowerPivot



## katecunn (Nov 19, 2014)

Hi,

I have searched everywhere and cannot seem to find an answer to this issue.

I have a couple of pivot tables built from a powerpivot query of a database on one of our servers.

These tables are showing similar information so i have applied a couple of slicers, such as owner and business divsion.

What i need to have is that all the coloums and rows stay there when i select certain slicers as these values are used in a lookup query to populate an overview table for key end users.

However no matter what i try when i choose a slicer it will only show the rows and coloums that have data in them, I am more concerned with the coloums then the rows remaining.

When i chose field settings -> layout & print, " Show Items withn no data" is greyed out

I have tried to create a calculated field at the powerpivot level to show 'IF(ISBlank)' because they are not blank untill i apply the slicers within my workbook.

I really hope someone can help me.


----------



## scottsen (Nov 19, 2014)

For clarity...

There are 2 places you do power pivot formulas, Calculated Columns (on the actual power pivot tables), and Calculated *Fields* (called Measures by old timers).  The latter is always dynamic and only evaluated when executed.  

To me it sounds like you are talking about a calculated COLUMN above, not a field?


----------



## katecunn (Nov 20, 2014)

Yes I just want to keep the exact amount of columns across in the pivot table no matter what slicers are applied.

Thanks


----------



## scottsen (Nov 20, 2014)

If you are using a measure (not a calc column), then your technique of testing IF (ISBLANK(), 0, ...) should certainly work.


----------



## RoryA (Nov 20, 2014)

Out of interest, what exactly does this mean:


> these values are used in a lookup query to populate an overview table for key end users


----------



## Tianbas (Nov 20, 2014)

Another option would be to use CUBE formulas instead of a Pivot for your overview report. This allows you to have a fix structure of your report that still works with slicers.

Just select your pivot and go to Options->OLAP Tools->Convert to formulas


----------



## GDRIII (Nov 20, 2014)

Word of caution, once you convert to OLAP, you can't undo it.


----------

