Data Model: Slicers Not Greying Out Items With No Data

Nations

New Member
Joined
Mar 2, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I'm running into issues with some of my slicers displaying the full list of items and not greying out items without any data.

I'm working with a data model with 4 data tables and 2 dimension tables, Location and Employee. Both dimensions have various rollups. I've created the relationships between each dimension and the 4 data tables and connected each slicer to the 4 data pivots.

Now, amongst the slicers within each dimension, the slicer display settings are working great. IE. if I have City -> State, when I select a state, all the cities without data are greyed out and moved to the end of the slicer. However, the Location slicers have no effect on the Employee slicers and vice versa.

Is there a way to have the 2 sets of dimension slicers affect each other? IE. if I select a State, I'd like all the employees without data to be greyed out. I've tried connecting the dimension slicers to each other but it didn't seem to do anything.

Appreciate any help, hopefully this didn't sound too confusing.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,654
Office Version
  1. 365
Platform
  1. Windows
Try this:
Right click on the slicer and select "Slicer Settings..."

Tick Hide Items with no data.

1616388017413.png
 

Nations

New Member
Joined
Mar 2, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the response. I ended up solving the issue.

The problem was the two dimensions weren't linked to each other so I combined them into a single dimension and created surrogate keys to link to the data tables. Things appear to be working now.
 

Forum statistics

Threads
1,140,928
Messages
5,703,218
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top