Tableau Hide Values in a Filter

//

Do you want to hide Nulls from a Tableau filter? This article shows two ways to hide Null or any other values from a filter.

NOTE – both technique removes the values from the data / worksheet. Also, if using the Context filter method, be aware of the affects that has on any FIXED LOD calculations.

Remove Null from filter by Duplicating the Dimension

To remove null from a Tableau filter by duplicating the field, follow these steps:

1. For this technique, we need a copy of the field containing the filter values. Therefore, duplicate the dimension (right-click on the dimension and ‘Duplicate’).

2. Put the duplicate field to the filter shelf. Next select the values you want to hide – i.e. Null – and click exclude on the right of the filter box.

exclude the null and other unwanted values from the filter

3. Put the original filter field to the filters shelf – there should be the original and the copy on the filters shelf

4. Show the original filter

5. Now there are two choices:

  • Add the duplicate dimension to context (right click on the pill to see this option). Next, set the filtering pill (i.e. not the duplicate), to only show values in context.
  • Leave the duplicate field out of context and set the filtering pill to show only relevant values.

Remove Null from a Filter using a Set

Another option to remove the null values from a Tableau filter is to use a set. Remember, this also excludes the values from the worksheet. Do the following to create the set without Nulls:

1. Create a set on the Dimension containing the filter values. Right-click on the dimension and Create – Set.

2. Exclude the Null (or other unwanted values) from the set

excluding null from a set in Tableau
Exclude Null and any other unwanted values

3. Put the new Set to the filter shelf. This should remove the Null and any other values excluded in the previous step. To check you can “Show members in set” and then “Show Set”.

tableau set with Show members in set selected

4. Put the filter field to the filter shelf, where it should automatically only include the items not excluded in the set.

Tableau filter with Nulls removed
The filter automatically excludes values that are excluded within the set

Hide Null from a Filter and Keep the values

Sometimes the data with the Null values is needed in the worksheet data. For example perhaps you want to sum total sales including those where the Region is unknown.

Therefore, to sum the correct total, the numbers can’t be excluded. In which case, either leaving the Nulls or renaming the Null values to something else is preferable.

The Tableau IFNULL formula can rename Null values in Tableau.

It is far simpler to remove the All from a quickfilter. See the Customise option of the quick filter settings.

Remove the Null from a filter from a secondary data source

When blending data sources in Tableau, if using a dimension filter from the secondary source, Tableau will include a Null at the top of the filter values list.

Unfortunately, there isn’t an option to get rid of this null from the filter.

The only workarounds I’m aware of are:

  1. Use a parameter as a filter – which has it’s own difficulties so won’t work for all use cases
  2. Switch the data sources, so the data source with the filter field becomes the primary data source (read the article on data blending for more information on this)
  3. Use the Tableau scaffold technique to produce a list of all possible filter combinations; use this as a primary source and blend in the other data as secondary

None of these options are ideal; an only relevant values option for filter in the secondary data source is preferable, but isn’t yet possible.

34 thoughts on “Tableau Hide Values in a Filter”

  1. thx for the post!
    is there any way to hide a value from a quick filter but without removing it?
    i.e something similar to select any of these values and always the one I hided for you
    that filter is expected to be dynamically populated based on an URL parameter
    thx!

    Reply
  2. Where is the Shower Fewer Values option? I am trying to show a DMA Map, and I would like to filter on specific values, but the NULL option is always there, despite there being no rhyme or reason for it being there.

    Reply
    • I’ve updated the post to add a bit more information about finding the ‘Show Fewer Values’ option in the quick filter.

      Reply
  3. Upon using the approach you described above, I want to take the values that I hide, and create another filter with those.

    Is this possible

    Reply
  4. Thank you so much!!!! Just what I wanted and it worked perfectly!!! I saved for my future reference and added more wording for someone else who may be abit confused on how to go about it… especially first-timers. See below:

    Create a duplicate copy of the field you want to filter. Click on show quick filter. Then it shows on the right side. Then click on the inverted triangle in the filter and select “Edit Filter”. The Filter pop up window appears. Select the values you want to hide and click exclude on the right of the filter box. To finish, ensure the field being used as the filter – not the duplicate which won’t be visible to report users (duplicate will be in worksheet, but actual field will be in dashboard) – is set to ‘Show Fewer Values’. This is set in the quick filter options. Click on the bars at the top right of any quick filter to ‘Show Fewer Values’ for that quick filter… if that field doesn’t show up, click on customize>Show more/fewer button then it should appear as bars at the top right hand corner.

    Reply
  5. Thank you! This was the last step in helping me solve the biggest problem I was having with my dashboard.

    Reply
  6. This solution is good but it does not work for filter on secondary data source. I have two views which has same data source. Both are joined using blending. One being primary and other being secondary. But unfortunately this solution cannot be applied on filter field present in secondary data source. Or if there is way, that would be ultimate

    Reply
    • You’re right, this solution wouldn’t work using a secondary source. You’d have to look at other options, such as using a parameter as a filter or using custom sql in the secondary source to hide what you don’t want visible at that level. I always find complexity is significantly increased once secondary data sources are brought into play.

      Reply
  7. @alberto
    Hey Alberto.. Did yo get a solution to your problem? I am having the same issue and have been scratching my head since yesterday to no good.

    Reply
  8. @awatson

    I have the same problem. I have a secondary data source with nulls which I would like to exclude. Were you able to find a solution using a parameter or custom SQL?

    Reply
  9. Hi,

    I have a related problem on a dashboard that displays a symbol map that combines a polygon layer with a point layer on a dual axis. The map is for a province, the polygons represent zones and the points are health facilities.

    I created two filters: one for the zones and another for the health facilities. When I select a zone, in the facility filter I can choose only from the relevant values for that zone which is perfect. The problem is that the first value for a health facility is NULL. If I select any facility, the point on the map that represents that facility is selected, which is exactly what I want but the background of the map disapears.

    That’s because the NULL values in the Facility filter correspond to the zone borders. And that is because when you create a map combining polygons and points you must combine the datasets for both in the data source. Therefore, in the data source in the Facilty field I have nulls for all the records that correspond to the polygon lines (= zone borders).

    Shortly, how can I hide the NULL value from the Facility filter whithout making the map desapear?

    Thanks,

    Reply
    • Hi Alex, I don’t think (but am more than happy to stand corrected) it’s possible to hide the NULL in this case, as you identify it’s needed in the data set. My recommendation, inelegant as it is, would be to title the filter ‘Health Facility – do not remove NULL’, or words to that effect – with the part about removing Null appearing under the Health Facility part of the title.

      Reply
  10. Andrew,

    This is great when connecting via SQL, but when connecting to Analysis Services the ‘Exclude’ box doesn’t appear in General tab on the Filter dialog. Is there any way to exclude a parameter that is in the cube? I’ve already checked ‘Relevant Values Only’ to exclude those that aren’t found in the cube…

    Bill Coulter

    Reply
    • The functionality within Tableau is drastically reduced when connecting to a cube, generally all of the heavy lifting needs to be pre-set in the cube and Tableau is just a pure visualisation layer.

      Reply
  11. When you say duplicate filters, do you mean create a copy of filter so that you have 2 filters in the Filter Box? I cannot duplicate an existing filter. Can you clarify please

    Reply
    • You duplicate the field being used a the filter, then put both the original and duplicate to the filters shelf. Therefore you do have 2 filters in the filter box, the original field and the duplicate of that field.

      Reply
    • As far as I know there isn’t a way to remove it from the filter if you want it to appear in the data. You could rename it to something more meaningful, maybe ‘Do not unselect’ or something similar.

      Reply
    • Hi Swathi,

      Did you find any workaround for this issue. If yes please reply back with the solution.
      I have similar issue but didn’t find any solution.

      Thanks in advance.

      Reply
  12. @Happy

    Thanks, It’s really helpful to me.

    I have one doubt

    I want to replace with ” NOT GIVEN ” in place of ” NULL ” is it possible can you give the solution pls

    Reply
  13. Try creating a calculated field with the formula IFNULL([CurrentFilterField],’NOT GIVEN’) in place of your existing filter field

    Reply
  14. Hi Guys !
    This is a good trick,
    but i need something a bit different. this regards to the NOTE:
    (“if using this technique it actually removed what you’ve hidden from the filter from the worksheet.)

    i want to hide “NULL” values from the filter – but KEEP it always ON.
    Meaning i want it to actually NOT REMOVE what i’ve hidden.

    Any ideas ? 🙂

    Reply

Leave a Reply to Happy Cancel reply