When there are no slicers present, and I filter a field using the pivot table, the other filters display the subset that is available within that option.
Ex. I have a transaction for multiple years. I filter Year to 2018. Displayable options in the Month field are Jan - Jun, since that is all that is available for this year.
When there are slicers, although I filter (using either slicer or pivot table filter) on Year 2018, Jan - Dec are available (pivot table filter) although there are no records for Sep - Dec.
I understand that with slicers Sep - Dec would be greyed out in the slicer, but I want the pivot table slicer to behave like when there are no slicers and only show me available options. Is this doable? I can use Excel 2010 or 2016.
I have tried doing "Right click on Pivot > Display > Uncheck "Show items with no data on rows" or "Show items with no data on columns" (For excel 16)" I can see that the boxes are unchecked and grayed out. I have also gone to each of the fields and under field settings have I went to "Layout & Print" and ensure that the box labeled "Show Items with No Data" is unchecked. Yet when I filter the pivot where there is only 1 row of data, other rows still show the full data set.
