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

katecunn

New Member
Joined
Nov 19, 2014
Messages
2
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Nov 19, 2014
Messages
2
Yes I just want to keep the exact amount of columns across in the pivot table no matter what slicers are applied.

Thanks
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
If you are using a measure (not a calc column), then your technique of testing IF (ISBLANK(), 0, ...) should certainly work.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Apr 29, 2014
Messages
101
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

Board Regular
Joined
May 30, 2014
Messages
88
Word of caution, once you convert to OLAP, you can't undo it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,576
Messages
5,637,183
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top