I built a Power Pivot table using Excel's data model. My report has two tabs that are very similar save for the Pivot Field in the rows: Size and UPC.
The Size tab functions as intended; each slicer is set to "Hide items with no data" and, as a result the value (blank) never appears in any slicer.
However, on the UPC tab, some slicers show (blank) in spite of being set set "Hide items with no data".
Why does this functionality break down on the UPC tab?
On the UPC tab, when I created the slicers, they all worked as intended until I connected the slicers to to the "hidden" pivot tables corresponding to the fields Supplier, Brand Franchise, and Brand. These three fields are pulled from connected tables in the data model, so clearly the issue is related to that.
Any help would be greatly appreciated. Thank you!
crossposting: https://chandoo.org/forum/threads/slicer-does-not-hide-items-with-no-data.58586/
and
https://www.mrexcel.com/board/threads/slicer-does-not-hide-items-with-no-data.1273206/#post-6268976
Please read this:--> https://excelguru.ca/a-message-to-forum-cross-posters/
One more example of the issue:
On the UPC tab, if you select a Supplier, you will still see all the sub-brands appear in the Sub-Brand slicer even if they don't have data. This problem is not on the Size tab.
By the way, my apologies--I realize the Brand Franchise slicer and another slicer on the UPC tab is not properly connected to all the pivot tables on that sheet--please ignore that issue. My original question still stands. Thank you!
The solution is to add the slicer for the row field in the main pivot table, and connect it to the other pivot tables on that sheet.
Credit to RoryA over on MrExcel.com for the solution, who said:
"On your Size sheet, you have Size as the row field in the top pivot table and it is also a slicer. That means that all the pivot tables that your slicers are connected to (which is all of them on that page) will always have the same filtered dataset."
Thanks to everyone who gave this their time of day!
