In Tableau, NULLs are the significant roadblocks while performing analysis. Because of them, the results might need to be corrected or meaningful. So, it is mandatory to deal with NULL values before analyzing the data. Tableau provides multiple functions to deal with NULL values, each with its own significance.
In this article, we will show you how to use the Tableau NULL functions, which include ISNULL, NOT ISNULL, IFNULL, and ZN, with an example of each. To demonstrate these NULL functions, we will use the Employee table Measure and Dimension columns.
Tableau NULL functions
Tableau has three types of NULL functions: ISNULL, IFNULL, and ZN. You can use them on both Measures and Dimensions. To get meaningful information, you can use these functions with a combination of IF ESLE, AND, IIF, and OR functions.
- ISNULL: If the expression result is NULL, it returns True. If it is not null, it returns False.
- IFNULL: It replaces all the NULL values with the user-specified value in the second argument. Otherwise, it returns the original values.
- ZN: It replaces all the NULL values with zeros. Otherwise, it returns the original values.
To demonstrate the Tableau NULL functions, first, we must create a calculated field to write the expression or use any built-in function. Right-click the empty space within the Measures shelf to select the Create calculated field.. option. Please visit the SQL Source and SQL Tutorial article to understand the Data Source.
Tableau ISNULL Example
This Tableau example uses the ISNULL function to check the Sales column for null values. In the below expression, we used the IF ElSE statement to write a message instead of True and False. So, it (ISNULL([Sales])) checks each row of the Sales column for the null value. If there is a NULL value, it returns Poor. Otherwise, the Table column returns Good. For the remaining charts in Tableau, please click here.
IF ISNULL([Sales]) THEN 'Poor' ELSE 'Good' END
To see the output of the ISNULL function, let me add that Message calculated field to the table Rows shelf. Looking at the output, you can see the Good message for the non-null values and the Poor message for NULLs. For more built-in functions >> Click Here!
The result will be the exact opposite if you use the combination of ISNULL and NOT function. It is beneficial to check multiple conditions. The below expression (NOT ISNULL([Education])) checks if it is not a NULL value inside the Education column. If it is True, return the Education filed text. If it is a null value, return the Occupation field message.
IF NOT ISNULL([Education]) THEN [Education] ELSE [Occupation] END
Tableau IFNULL Example
This Tableau example uses the IFNULL function to check the null values in the Occupation column. The below expression replaces the NULLs in the Occupation column with the corresponding Education column text. If both theOccupation and Education column values are NULLs, the IFNULL function returns the NULL value. You see the changes in the image below as we hit the apply button.
In this example, we used the SUM(Sales) as the first and 999 as the IFNULL function second argument. It will check if there are nulls in the Sales column. If True, replace them with 999.
Tableau ZN NULL function Example
This example uses the ZN function to deal with the null values in the Sales measure. The below expression replaces the NULLs in the Sales column with Zeros.
The screenshot below shows all the available NULL functions, resulting in a single table against the Sales measure. You can see how these three ISNULL, ZN, and IFNULL functions deal with NULL values in Tableau.