"Include new items in manual filter" greyed out if the field is in the filter area of a pivot table

Copper Contributor

I'm using Excel in Office 365. I have a pivot table where one of the fields in the source data can change over time (new products being added). In my pivot I need to exclude just one product, so I have product in the filter with that product deselected.

The problem is that when new product codes come into the data, because one product code is already deselected, all the newly added product codes come into the filter unselected. This makes the data in the pivot incomplete.

How can I ensure that the filter always includes new products?

If I moved the product field into the rows area of the pivot I could check the box to include new items, but that makes the pivot table huge and unwieldy - that doesn't work for my purposes.

Does anyone have any workaround?

Thanks

9 Replies

@PaulMBACB Are you using Power Pivot? I was not able to duplicate this problem using either a regular table or Power Query as the pivot table source.

 

One possible workaround is to open the Power Pivot window (Manage Data Model) and add a calculated column called "Status" to the applicable table with the following formula:

 

=IF(Table1[Code]="ExcludedCode", "Inactive", "Active")

 

Return to the workbook, add the new "Status" field to the Filters section of the pivot table and filter by "Active".

 

You could also simplify this even further by naming the calculated column "Active" with the following formula:

 

=Table1[Code]<>"ExcludedCode"

 

...then filter by TRUE. However, this wouldn't make sense visually if you ever needed to clear the filter, as the report would then read "Active: All".

@djclements Thanks for your reply.  Honestly, I'm not quite certain what I'm using.  It's not a basic pivot table where the source is a table of data somewhere in the spreadsheet.  It's a mechanism provided by our helpful IT guy to allow us to get data from the data warehouse into Excel.  It looks and acts just like a normal pivot table, but the data connection says the connection type is "OLAP query".

@PaulMBACB Sorry, I'm not able to test Power View with an OLAP query on my system. You could pass my suggestion on to your IT guy and see if it's feasible, or wait for someone else to reply here.

 

Best regards.

@PaulMBACB 

In general based on data model PivotTable shall keep filters, e.g.

before

image.png

after adding new records and refresh

image.png

labels are added and filter is kept

image.png

and that is OLAP query to local data model

image.png

Right now I can't test the same with the external cube, i.e. may connect to some but can't add extra records toc heck the PivotTable behaviour.

Hi Sergei. Your screenshots are showing the same as I see. In your example, I need new items (e and f, in this case) to automatically be included rather than excluded in the filter when they are introduced into the data. Is there any way this can be achieved?

@Sergei Baklan If I'm not mistaken, I believe @PaulMBACB is asking about the "Include new items in manual filter" setting, which is greyed-out when the field is placed in the "Filters" area of the pivot table, as shown below:

 

Pivot Table Filters AreaPivot Table Filters Area

Field Settings Greyed-OutField Settings Greyed-Out

 

I was able to reproduce this behavior using Power Pivot. Perhaps it's a result of using the Data Model as the pivot table data source, although I was not able to test this theory further.

@djclements Yes, thanks for the clarification. It is when the field in question is in the filters area of the pivot.

@PaulMBACB , @djclements 

I misunderstood the task, sorry.

If we need to filter only one value, workaround could be not add such field to filters and no use manual filter, use Label Filters with Does Not Equal

image.png

In the sample I added not equal to "b". As you may see it's checked in manual filter, but PivotTable is filtered on it and added field values will be checked

image.png

Perhaps you don't need such field in PivotTable, when you may hide column with it.

If above is not suitable when only to work with data model. Here it depends do you work with local one or with external cube. If the latest instead of default "Model" in command text of connection you may define proper MDX query. However, not simple task if you never worked with MDX.