Slicer does not hide items with no data

[PowerPivotinExcel]

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. I tried to upload my file but I can't seem to get it small enough--if you know the file limit and want to take a look, let me know! Thank you!

 

[Debaser]

'Hide items with no data' does not mean that (blank) will never appear. If there is source data that doesn't have a value for that slicer field, it will display blank because blank does have data matching it. It sounds like something in the pivots you have linked to has values for which the values in your slicer field are empty.

 

[PowerPivotinExcel]

'Hide items with no data' does not mean that (blank) will never appear. If there is source data that doesn't have a value for that slicer field, it will display blank because blank does have data matching it. It sounds like something in the pivots you have linked to has values for which the values in your slicer field are empty.

Hi Debaser, thanks for your response. In my case, blank *does not* have data matching it. For example, one of my slicers is a "date" field. Every line of data in my source has a date. Why would (blank) show in the slicer when I've set it to hide items with no data? Why would (blank) not show on a different pivot table built from the same source?

Also, I forgot to mention, the bigger problem is that there are items appearing in the slicer (not just "(blank)" ) that absolutely do not have any data associated with them.

Also, I have cross-posted this question on Mr Excel and My Online Training Hub. Here are those links:

Slicer does not hide items with no data

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....

www.myonlinetraininghub.com

Slicer does not hide items with no data

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...

www.mrexcel.com

Thank you!

 

[Debaser]

From the sounds of that last part, I’d say your relationships aren’t set up correctly.

 

[PowerPivotinExcel]

From the sounds of that last part, I’d say your relationships aren’t set up correctly.

My relationships are set up to facilitate a custom sort order for a few of my slicers, so I linked them from many to one instead of one to many. Is that a "no-no"? If I did it the other way around, I believe the functionality I'm looking for wouldn't work. Is that why this seems to break down on the table where each row is at product level instead of aggregated under a different Pivot field?

If you follow the link to my post on myonlinetraininghub.com, you can download an anonymized version of my data to take a look at the relationships. Here's a snap:

 

[Debaser]

I’ll try and have a look at your sample when I can.

Linking many to one is the same as vice versa so I’m not really sure what you mean, or what sort order has to do with relationships. I will just note for now that filtering only goes from one to many, not the other way.

 

[PowerPivotinExcel]

Thanks!
Just to give you some background context:
I have a slicer for Time, a slicer for Price Segment (New Price Segment), and a slicer for Sub-Brand. The slicer orders the items in these fields alphabetically by default, but I don't want that. I also don't want to add a "helper digit" at the front of each item to force the slicer to sort the way I want. Hence, the connected tables.
Since the connected tables have the fields and sort order I want to filter by, I set up the relationships from many to one.
This works for every other Pivot Field I place in "rows" but always seems to break down at the item level (or when I put the product in the rows).

 

[Debaser]

As I said, the filtering only works in the direction of 1 to many. If you apply a slicer that affects the Range table, that filtering will not affect any other tables on the 1 side of the relationships to that table. Hence, if you filter Supplier, for example, which is connected to the Range table, it will not affect any slicers based on Table1, Table2 or Table3.
If you want that to happen, you could add the sort orders as calculated columns in the Range table - either using RELATED in the data model, or in Power Query.

 

[Debaser]

Although, having said that and then looked at your Size tab, I am currently at a loss as to why that works and UPC doesn't! I think I've spent too much time in PowerBI recently and have forgotten how slicers in Excel work...

 

[Debaser]

OK, so in case you didn't see my answer elsewhere, it's because you need all the slicers on the UPC sheet connected to all the same pivot tables, and you also need to add a slicer for any row/column fields added to the pivot table(s) so that all the filtered datasets are the same.

 

[PowerPivotinExcel]

OK, so in case you didn't see my answer elsewhere, it's because you need all the slicers on the UPC sheet connected to all the same pivot tables, and you also need to add a slicer for any row/column fields added to the pivot table(s) so that all the filtered datasets are the same.

Thank you!! Confirmed that this is the solution! Thanks so much for giving this your time of day!