Replicate "Show Items with No Data Filter" from Normal Pivot Table in Data Model Pivot Table

[spottiedog]

Hey All,
Seriously desperate. Hopefully this is an easy one I'm wanting to replicate the "show item with no data" filter functionality of a "normal" pivot table into a "data model" pivot table.

Functionality of the "normal" pivot table i'm looking to replicate:

• I created a "normal" pivot table which has a "yes" filter selected.
• The filter has the "show items with no data filter" function applied so the filter value of "yes" will remain when there is no data in the source.
• The outcome of this is to always keep the "yes" filter applied regardless if there's data in the source table.

I need to replicate the functionality described above for a "data model" pivot table. This type of pivot table has the "show item no data function greyed out" which means when the data is deleted from the source table, the "yes" filter is removed and more importantly, when new data is copied in, the filter then reverts to "all" data which is killing my model.

Any help GREATLY appreciated, hoping this is a simple one.

Josh.

 

[sandy666]

?did you try Pivot Table Options - Display and then

 

[spottiedog]

Hey Sandy, thanks for the reply!

I tried selecting both of those options but the "yes" filter is still being remove when I delete the source data.

When I add the source data back in, it's still resting the filter to "all". Hrm!

 

[sandy666]

try second option only

anyway is hard to say something more without "live" example so you need to try, try and try ?

 

[spottiedog]

I've uploaded a sample book to demonstrate the issue if that helps ? If you delete the data and add it back in, it will demonstrate the problem

https://wetransfer.com/downloads/281c0553e0404ad7a7d8e3cf2e68de5020200323022216/343c36641c7a16dc2bc4707dff8e4ef920200323022216/ef73ba

 

[sandy666]

I did that few times but still see yes not all
without source data I cannot refresh of course but I can use filters anyway

maybe describe step-by-step what are you exactly doing (in points)

or maybe say what you really want to achieve

 

[spottiedog]

Really appreciate the help sandy!

Image 1 - shows the blue source data in there
Image 2 - shows the "yes" filter
Image 3 - shows blue source data being removed and pressing refresh all
Image 4 - shows "data model" pivot table filter now remove
Image 5 - shows "normal" pivot table filter still "yes"

If that blue data is copied back in, the "data model" pivot table will have an "all" filter now but they "normal" pivot table will still retain the "yes" filter

 

[spottiedog]

Whoops left off image 5!

 

[sandy666]

Got it.
"normal" Pivot Table has own cache so you see what you see

with Power Pivot go to data model after deleting source data and you will see blank table with headers only so how do you want to see yes if it doesn't exist?
there are three "parameters" : yes, no and blank , if you delete one, in this case yes , filter will reset to all
Imagine data model is like cache for Power Pivot

 

[sandy666]

read here Pivot Cache