How to Get Distinct Values in Tableau

Counting unique items in your data is one of the most common tasks in data analysis. Whether you need to know how many unique customers made a purchase, which distinct products were sold, or the number of unique website visitors from a specific region, Tableau provides several ways to get the answer. This guide will walk you through the primary techniques, from the simple drag-and-drop method to more advanced calculations for complex scenarios.

The Easiest Method: COUNTD() for a Simple Count

When you just need a single number representing the total count of unique values, Tableau's built-in COUNTD() function is your go-to solution. It stands for "Count Distinct" and does exactly what its name suggests.

This is perfect for dashboard KPIs and quick summaries. For example, if you want to display a single card showing "Total Unique Customers" or "Distinct Products Sold," COUNTD() is the fastest way to get there.

How to Use COUNTD()

Let’s use the Sample - Superstore dataset included with Tableau to find the total number of unique customers.

1. Connect to the Sample - Superstore data source.
2. In the Data pane on the left, find the Customer Name dimension.
3. Right-click on Customer Name and drag it onto the Text card in the Marks pane.
4. When you release the mouse button, a menu will pop up. From this menu, choose CNTD(Customer Name).

Tableau will instantly display a number, which is the distinct count of customers in your dataset. You've just created a simple KPI!

Alternatively, you can create a calculated field for more flexibility:

1. Click the down arrow at the top of the Data pane and select Create Calculated Field.
2. Name your calculation something intuitive, like "Unique Customer Count."
3. In the formula box, type:

COUNTD([Customer Name])

1. Click OK. You now have a new measure in your Data pane that you can drag into any view, just like any other field.

When to use COUNTD(): Use this method whenever you need a straightforward, aggregated count of unique items. It's efficient, easy, and ideal for high-level dashboard metrics.

How to List Distinct Values

Sometimes, a simple count isn't enough. You might need to see the actual list of unique values. For example, you might want to create a table showing every distinct product sub-category or populate a filter with all the unique regions.

This is Tableau's default behavior. When you bring a dimension into a view, Tableau automatically groups by the unique members of that dimension.

Generating a Distinct List

To see a list of unique values, follow these steps:

1. Find the dimension you want to list, like Sub-Category.
2. Drag the Sub-Category dimension onto the Rows shelf.

That's it. Tableau will display a vertical list of every distinct sub-category found in your data. You can then add a measure like Sales to the Columns shelf to create a bar chart, or drop Sales onto the Text card to create a summary table. This fundamental action is the basis for most visualizations in Tableau.

Advanced Techniques for Distinct Counts

What if your question is more complex? For instance, "How many unique customers purchased in each state?" or "Show me only the first order for each new customer." These scenarios require more advanced tools like Level of Detail (LOD) Expressions or Table Calculations.

Using FIXED LOD for Deeper Analysis

Level of Detail (LOD) expressions allow you to calculate values at a different level of granularity than the dimensions in your view. A FIXED LOD is incredibly useful for calculating a distinct value for a specific dimension and then reusing that value across different visualizations.

Let's find the number of unique customers per region, a calculation that will remain accurate even if we remove the Region dimension from our view later.

How to Create a FIXED LOD for a Distinct Count:

1. Create a new calculated field. Let's call it "Unique Customers per Region."
2. Enter the following formula:

{ FIXED [Region] : COUNTD([Customer Name]) }

Breaking down the formula:

• { ... }: The curly braces signal to Tableau that this is an LOD expression.
• FIXED [Region] :: This tells Tableau to perform the calculation for every unique Region in the dataset, regardless of what's currently shown in your visualization.
• COUNTD([Customer Name]): This is the aggregation we want to perform - our familiar count distinct function.

Now you can build a view. Drag Region to the Rows shelf and your new "Unique Customers per Region" calculated field to the Text card. The result is a table showing each region and its specific unique customer count.

The real power here is that this calculation is now portable. It's tied to the Region field in the background. If you create a view that doesn't include Region, you could, for example, calculate the average number of unique customers across all regions by simply averaging this new calculated field.

Using Table Calculations to Filter for the First Instance

Another common challenge is dealing with transactional data where a value, like a customer's name, appears multiple times. What if you want to create a list of customers but only show their very first order date?

This is a great use case for a table calculation, which performs computations on the data currently in your visualization.

How to Use a Table Calculation like INDEX() or FIRST():

1. Start by creating a table. Drag Customer Name to the Rows shelf, and then drag Order Date to the Rows shelf next to it. Be sure to set Order Date to be a discrete (blue pill) Exact Date.
2. You'll notice that many customers have multiple rows, one for each order. Our goal is to keep only the first one for each customer.
3. Create a new calculated field. Name it "Instance Filter."
4. In the formula box, type:

INDEX() = 1

(You could also use FIRST() = 0, which achieves the same result. INDEX() is often more intuitive for beginners, as it numbers the rows in a partition 1, 2, 3...)

1. Drag your "Instance Filter" calculated field onto the Filters shelf, and in the pop-up, check the box for True.
2. This is the most critical step: You need to tell Tableau how to apply this calculation. Right-click the "Instance Filter" pill on the Filters shelf and select Edit Table Calculation.
3. In the Table Calculation dialog:

This configuration tells Tableau: "For each unique Customer Name, number their orders based on the Order Date. I only want to keep the one where the index is 1 (the first)." Your table will now show a unique list of customers, each with just their initial order date.

Displaying a Distinct List in a Tooltip

One of the coolest things you can do in Tableau is add rich, contextual information to your tooltips. A common request is to hover over a chart (e.g., Sales by Region) and see a list of the top distinct customers in that region.

This is accomplished with a feature called "Viz in Tooltip." Essentially, you build a second worksheet that contains the unique list and then embed it into the tooltip of your main chart.

Step-by-Step Guide to Viz in Tooltip:

1. Create your main chart. Create a simple bar chart with Region on the Columns shelf and SUM(Sales) on the Rows shelf.
2. Create your tooltip list. Create a new worksheet and name it something like "Tooltip - Customer List."
3. Embed the list in your main chart's tooltip.

Now, when you hover over a bar in your main chart - for example, the "East" region - the tooltip will automatically filter the embedded sheet to show only the distinct customers from the East region. It’s a powerful way to add detail without cluttering your dashboard.

Final Thoughts

From a lightning-fast COUNTD for your main KPIs to advanced FIXED LODs for establishing a single source of truth, Tableau equips you with a full toolkit for handling distinct values. By mastering these different methods, you can answer a wide range of business questions and build more insightful and interactive dashboards that go beyond basic summaries.

Figuring out the right formula or table calculation configuration can sometimes be a time-consuming process of trial and error. At Graphed, we've focused on removing that complexity. Instead of writing LOD expressions or setting up viz-in-tooltip worksheets, you can simply ask for what you need in plain English. Queries like "what was the unique customer count per region last month?" or "show me my sales pipeline from Salesforce" are translated into interactive dashboards instantly, allowing you to focus on the insight, not the setup. You can try Graphed for free and get real-time answers from your data in seconds.