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.
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
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”.
4. Put the filter field to the filter shelf, where it should automatically only include the items not excluded in 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:
- Use a parameter as a filter – which has it’s own difficulties so won’t work for all use cases
- 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)
- 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.
Thanks, just what I was looking for.
Awesome – thanks for the post, it was just what I needed
Thanks for sharing! This is exactly what I was trying to figure out.
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!
I’m not sure what you mean Alberto. Can you send a workbook explaining what you want to do? [email protected]
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.
I’ve updated the post to add a bit more information about finding the ‘Show Fewer Values’ option in the quick filter.
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
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.
Thank you! This was the last step in helping me solve the biggest problem I was having with my dashboard.
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
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.
@Happy
Happy,
Thank you so much for adding the requirement to make a copy of the field. It really helped me out :).
@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.
@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?
Very helpful, thank you!
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,
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.
This is really helpful. Thanks.
@Joan
You are very welcome, Joan!!
Thanks for sharing! This is exactly what I was trying to figure out.
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
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.
Very helpful! Thank you! Saved me from creating a parameter! 🙂
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
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.
i want to hide Null from filter but dont want to hide null values in view how to do that
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.
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.
@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
Try creating a calculated field with the formula IFNULL([CurrentFilterField],’NOT GIVEN’) in place of your existing filter field
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 ? 🙂
You sir, are a life saver!
Thanks! Using the duplicate field to exclude NULL did the trick.