What is Tableau Count Distinct Function?
Tableau Count Distinct function is one of the aggregate functions in Tableau that allows you to count distinct values in a field or expression. Tableau offers multiple ways to evaluate an expression or field and provides the distinct values contained in the field or expression. Distinct counts are highly useful analytics when it comes to identifying unique values in a dataset. For example, you can use the count distinct function to determine the unique website hits in a web portal, the distinct number of patients in a hospital, or the distinct number of customers in a bank holding account.
Key Takeaways
- Tableau Count Distinct function allows you to count distinct values in a field or expression.
- This is highly useful analytics that can be used to identify values in a dataset.
- Tableau offers multiple ways to evaluate an expression or field and provides the distinct values contained in the field or expression. You can use the calculated field or Tableau user interface to use the count distinct function in your dataset.
- You can apply filters, conditional logic, or LoD (Level of Detail) expressions to count distinct values in your dataset.
- Tableau count distinct function supports counting distinct values across multiple dimensions.
Syntax
The Tableau COUNTD function has the below syntax:
COUNTD(expression)
- This aggregate function returns the number of distinct items in a field or expression, and it doesn’t include any null values.
- For example, the COUNTD([Product Name]) function will return the total unique product names contained in the Product Name field.
How to use the Count Distinct function Tableau?
There are different ways you can use the Tableau Count Distinct function, i.e., COUNTD in Tableau.
Using Calculated Field
To use the count distinct function using the calculated field in Tableau, follow the steps below
Step 1: Import the data into the Tableau interface
Step 2: Navigate to Analysis – Create Calculated Field.
Step 3: In the Calculation Editor, specify a name and logic using the FIXED LOD expressions.
Here, we have used Tableau count distinct multiple fields, i.e., Category and Sub-Category to count the distinct customers.
Step 4: Drag Region, Category, and Sub-Category to the Rows shelf and the newly created calculated field to the Columns shelf. Tableau will create a crosstab or text table like the one below.
As you can see, by using the LoD expressions in a calculated field, we have performed Tableau count distinct multiple fields.
Using Tableau Interface
We will continue working on the same example to demonstrate how you can use the Tableau interface to count distinct values in Tableau.
Step 5: Drag the Customer Name to the Column shelf. Tableau will expand the visual to display the customer names, as shown below.
Step 6: Click on the down arrow next to the Customer Name in the Column shelf and select Measure – Count (Distinct) from the context menu.
Tableau will update the visualization to display a bar chart in the view.
Step 7: Click on the Show Me toolbar and then select the text tables icon.
Here, Tableau has updated the view with distinct counts using the COUNTD function, both in the calculated field and in the Tableau User Interface.
In the next section, we will look at some of the examples using count distinct function in Tableau which can act as a reference point for your data visualization requirement.
Examples
In this section, we have demonstrated different examples of using the count distinct function in Tableau.
Example #1
In this example, we will use the COUNTD function by creating a calculated field in Tableau using the Dress Rental Prices dataset. It is a collection of rental prices for different dresses captured from a peer-to-peer lending platform based in the UK. It contains details such as dress brand, dress name, color, categories, etc.
Here’s how you use the COUNTD function in a calculated field in Tableau.
Step 1: Connect to the Dress Rental Prices dataset from the Tableau Public or Desktop application. Then, navigate to the Data Source tab in the Tableau application to view the imported dataset.
Step 2: Navigate to a new worksheet. Click on Analysis – Create Calculated Field.
Step 3: In the Calculation Editor window, specify the name of the calculated field and the logic to calculate the value.
Here, we have created a field called Total Dress Names to count the total dress names using the COUNT function. Click on OK.
Step 4: Now, create another calculated field by clicking on the down arrow next to the Search box in the Data pane and selecting the Create Calculated Field.
Step 5: In the Calculation Editor window, specify the name of the calculated field. Use the COUNTD function to count the distinct names of the dresses.
Here, we have created a field called Total Unique Dress Names using the COUNTD function.
Click on OK.
Step 6: Drag the Brand field to the Rows shelf. Drag both the calculated fields created earlier to the Columns shelf. Tableau will automatically create 2 bar charts in the view.
Step 7: Choose the Text table option under the Show Me toolbar.
Step 8: Drag the measures to the Color. Click on Color – Edit Colors. In the Edit Colors screen, select the Red-Green Diverging color palette option for the view.
Tableau will update the color-coding of the view as shown below. Now, your visualization is ready.
Example #2
In this example, we demonstrated the use of the count distinct function through the Tableau interface. For this demo, we have leveraged the Computer Science Students Career Prediction dataset. The dataset contains information about computer science students from a fictional university and includes attributes such as Student ID, Name, Gender, Age, GPA, Major, Interested Domain, Projects Undertaken, and Skills in Python, SQL, and Java.
To use the count distinct function in Tableau using the Tableau interface, follow the instructions outlined below:
Step 1: Import the Computer Science Students Career Prediction dataset into the Tableau Desk. To do so, navigate to File – Open and select the file from your machine.
Step 2: In a new worksheet, drag the Name field to the Columns shelf and the Interested Domain field to the Rows shelf. Tableau will create a text table, as provided below.
Step 3: Right-click on the Name field in the view and then select Measure – Count.
Tableau will create a bar chart in the view, as shown below.
Step 4: Drag the CNT(Name) and drop it to the Columns shelf to add one more CNT(Name). Right-click on the 2nd CNT(Name), and choose Measure(Count) – Count(Distinct).
Tableau will update the view as shown below. The 1st bar chart shows the total count of names whereas the 2nd one shows the distinct count of names.
Step 5: Change the visualization to the text table in Tableau. As you can see, the Tableau view now displays both the total count and the distinct count of students interested in different domains.
Example #3
In this example, we will create a visualization in Tableau using the Tableau count distinct if function. We have used the Customer360Insights dataset to create this visualization. This dataset is a collection of meticulously designed information that mirrors the multifaceted nature of customer interactions within an e-commerce platform with a wide array of variables.
To create a visualization in Tableau using the Tableau count distinct if function, follow the instructions provided below:
Step 1: Connect the Customer360Insights data to Tableau Public or Desktop using File Navigator.
Step 2: In a new worksheet, right-click on the Data pane and select Create Calculated Field option from the context menu.
Step 3: In the Calculation Editor screen, create a field named Distinct count of Australian Customers. We have used the Tableau count distinct if function to calculate the distinct count, as shown below. This field counts the total distinct customers who are from Australia. Click on OK to save the changes.
The newly created calculated field is displayed in the Data pane, as shown below.
Step 4: Drag fields Category and Product from the Data pane to the Rows shelf. Drag the Measure Names to the Columns shelf. Tableau will create a text table, as shown below.
Step 5: Remove all the Measure Names from the Marks card except the Distinct count of Australian Customers field.
Step 6: Drag the Full Name to the Columns shelf. Tableau will update the view by displaying all the customer names in the view column, as shown below.
Step 7: Right-click on the down arrow next to Full Name and select Measure – Count.
Tableau will update the visual to a bar chart with the total count of customer names, as shown below.
Step 8: Select the CNT(Full Name), hold the control key, and drag it to the Columns shelf. Click on the 2nd CNT(Full Name) field and select Measure(Count) – Count(Distinct), as shown below.
As you can see, Tableau has created 2 bar charts. i.e., one for the CNT(Full Name) and the other one for Distinct count of Full Name (CNTD(Full Name)).
Step 9: Click on the Show Me toolbar in the Tableau view and select the text table icon from the available options. Now, your final Tableau visualization is ready.
Important Things to Note
- Tableau Count Distinct function doesn’t include any null values for counting distinct values.
- You can only use dimensions in this function, measures are not supported.
- Tableau Count Distinct function is not recommended for continuous fields given the usability and resources used.
- Tableau count distinct function can be used with other aggregate functions for creating data visualization.
- For large datasets, you may experience slowness or performance implications on the Tableau Count Distinct function.
- Consider using alternatives to Tableau count distinct functions, especially in huge datasets containing text fields or string values.
Frequently Asked Questions (FAQs)
Yes, you can use Count Distinct in calculated fields or formulas. For example, you can use the COUNTD function within a calculated field or formula containing conditional logic to define your visualization.
Tableau doesn’t consider any NULL values when performing Count Distinct. To handle the NULL values, you may consider using logical expressions or other conditions in your calculation.
There are multiple ways to count distinct values based on certain conditions or filters. These include
Using conditional logic within a calculated field using a logical operator or function (IF-ELSE, etc.)
Using Level of Detail (LOD) expressions (FIXED, INCLUDE or EXCLUDE)
Yes, you can count distinct values across multiple dimensions in Tableau. You can the Level of Detail (LoD) expressions such as FIXED, INCLUDE or EXCLUDE for counting the distinct values across multiple dimensions.
Recommended Articles
Guide to What Is Tableau Count Distinct. We learn how to use the count distinct function in tableau with syntax, examples, and points to note. You can learn more from the following articles –
Leave a Reply