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

[katecunn]

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]

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]

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

Thanks

 

[scottsen]

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

 

[RoryA]

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]

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]

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