Forum Discussion

lrroberts's avatar
lrroberts
Copper Contributor
Apr 21, 2026

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:

  1. I can't find a way to hide (blank) from the slicers, because I don't know where it's coming from.
  2. 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!

6 Replies

  • Ewelina1's avatar
    Ewelina1
    Copper Contributor

    Hi!
    I have some suggestions to look at that may be helpful:

    1. take a look at the source file (not in the query), if there are any blank lines. If not, try deleting blank lines under data just in case
    2. If there are any connections based on two columns, check if there is a chance that one of them sets a different set of values. Maybe one is a text column, while the other is a number column.

    Is there a chance you could upload a screenshot of it?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    That could be incorrect relationships. Or missing foreign key in dimension table. Perhaps something else, but in any case that's better to discuss on sample file.

    • lrroberts's avatar
      lrroberts
      Copper Contributor

      Thanks for your reply. I apologize, but I cannot figure out how to attach a sample file in this thread - is it something I need to provide a link to? Or would a series of screenshots showing relationships in the data model be preferred?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        Again, you are to be sure keys in fact table(s) are not missed in dimension table. Simple case - you have dimension table Date with all dates in years from 2024 to 2026. But in fact table Sales you have transaction for December 2023. In ths case slicer will be with blank row. You may chack that yourself. File is to check what could be else if above is not the case.

  • Hecatonchire's avatar
    Hecatonchire
    Iron Contributor

    Hello

     

    Try this : Set "None" here (PivotTable options) and refresh ?

     

     

    • lrroberts's avatar
      lrroberts
      Copper Contributor

      That option is grayed out for me, I believe because the pivot table is derived from the data model. Please let me know if there's something I'm missing though! I appreciate your suggestion.