Forum Discussion
Slicers show (blank), but no blanks exist in the data model
My workbook's purpose is to function as a decision tool. Providers select services they offer using a series of slicers. Their responses filter a pivot table, which shows them which license they should apply for. Some services may be required (or not permitted) under certain licenses.
Each slicer represents a service category/type, and slicer options are the specific criteria/specific services used to determine the best license. All options are mutually exclusive (i.e., none of them appear in multiple service categories).
In the file, the 'status' variable indicates whether a specific service can be offered with a particular license. There are 4 statuses:
- Required: the provider must offer this service to qualify for X license (slicer option must be selected)
- Optional: the provider may offer this service but doesn't have to in order to qualify for X license (slicer option can be selected but doesn't have to be)
- Not applicable: the service is irrelevant to X license (selecting slicer option has no effect on recommended license)
- Cannot provide: the provider cannot offer this service to qualify for X license (slicer option cannot be selected)
The main issue: In all slicers, (blank) shows as one of the options. There are no blanks in the reference tables. All "real" slicer options also line up with something in the license requirements table... so there aren't any mismatches in the data model that I've been able to find. This is affecting my workbook in two ways:
- I can't find a way to hide (blank) from the slicers, because I don't know where it's coming from.
- When (blank) is deselected from a slicer, the filtered pivot shows the number of criteria in each status column for the corresponding service category.
- Example: The population served service category has 2 valid answers (1 of which is optional and 1 of which is not allowed for License A). When (blank) is deselected from the population served slicer, the filtered table shows 1 in the optional column and 1 in the cannot provide column for License A.
Other info:
- Allowing multiple selections is critical to the final product, so that will have to be done when the file is opened.
- I tried creating tables for service type, status, and service types/their corresponding options, just in case the data model didn't like the original.
- These tables are currently part of the data model, but they don't seem to make a difference either way.
- There are many combinations of services that could be offered. Some act as exclusion criteria for other licenses, so slicers shouldn't be connected to each other. Therefore, I've unchecked the 'hide items with no data' and 'visually indicate items with no data' slicer options.
- The final workbook will include more license types and additional functions. I removed several licenses to make the file shorter, but all service categories and options are present (since that's where the problem seems to be).
I'm not sure how to attach the workbook, but has anyone had this issue before? Is there an issue in the data model somewhere? What am I missing? Any help would be much appreciated!
1 Reply
- HecatonchireIron Contributor
Hello
Try this : Set "None" here (PivotTable options) and refresh ?