How to Count Unique Values in Power BI

Knowing how to count the number of unique entries in a dataset is one of the most fundamental skills in data analysis. While a simple row count tells you the total volume of transactions or events, a unique count answers much more specific business questions, like "How many distinct customers made a purchase?" or "How many different products were sold this month?" This article will walk you through several methods to count unique values in Power BI, from simple point-and-click options to more powerful and flexible DAX formulas.

Why Count Unique Values?

Imagine you run an online store and have a table with 1,000 sales transactions for last month. A simple count tells you that you made 1,000 sales. That's useful, but it doesn't give you the full picture. A unique count can provide much deeper insights:

• Unique Customers: If you count the unique customer IDs, you might find that only 250 distinct customers made those 1,000 purchases. This tells you that you have a loyal, recurring customer base.
• Unique Products: Counting the unique product names might reveal that only 50 different products were sold, leading you to investigate which items are most popular.
• Unique Clicks on a Campaign: If you're analyzing web data, counting unique user IDs who clicked an ad tells you your true reach, filtering out repeat clicks from the same enthusiastic person.

In short, counting unique values helps you understand the breadth and variety within your data, moving beyond simple totals to uncover more meaningful business intelligence.

The Simple Ways: Using the Power BI Interface

For quick checks and straightforward visuals, Power BI has some built-in tools that don't require writing any code. These are perfect for getting started.

Method 1: The One-Click "Distinct Count"

The easiest way to get a unique count is right within the report view when building a visual. This is ideal for summary figures, like those you’d find in a KPI card.

Follow these steps:

1. Select a Visual: In your Power BI report canvas, add a Card visual. Cards are designed to show a single, important number.
2. Add Your Data: From the Data pane, drag the column you want to count into the 'Fields' well of the Card visual. For instance, you might drag the 'Customer Name' column from your sales table.
3. Change the Summarization: By default, Power BI might try to show the First or Last customer name alphabetically. To change this, click the small downward arrow next to the field name in the 'Fields' well. A dropdown menu will appear.
4. Select 'Distinct count': In the menu, simply select Distinct count. The Card visual will immediately update to show the number of unique values in that column.

That's it! You've just calculated a unique count without any formulas. This method also works for axes and values in charts and tables, allowing you to quickly analyze unique counts across different categories.

Method 2: Counting Uniques in Power Query Editor

Sometimes you need to find a unique count while you're still cleaning and preparing your data. The Power Query Editor (the 'Transform data' window) is the perfect place for this kind of exploratory analysis.

This method doesn't create a dynamic measure for your report, but it's great for a quick check during the data transformation process.

1. Open Power Query: From the 'Home' tab in Power BI Desktop, click Transform data.
2. Select Your Column: In the Power Query Editor, find the table and column you're interested in and click on the column header to select it.
3. Find the Statistics Tool: Go to the Transform tab in the ribbon. In the 'Number Column' or 'Text Column' section, you'll find a dropdown button for Statistics.
4. Count Distinct Values: Click on 'Statistics' and then select Count distinct values from the list.

Power Query will perform the calculation and display the result as a single value. It's important to remember that this is a transformation step. If you keep this step, your entire data table will be replaced with this single number. So, this trick is best used to quickly find an answer and then remove the "Counted Distinct" step from the 'Applied Steps' pane on the right before you continue shaping your data.

Level Up: Counting Unique Values with DAX

While the interface methods are convenient, the true power and flexibility for analysis in Power BI comes from using Data Analysis Expressions (DAX). By writing a DAX formula to create a measure, you create a reusable calculation that can be used in any visual and that responds dynamically to filters and selections in your report.

The Workhorse: The DISTINCTCOUNT Function

The most direct and commonly used DAX function for this task is, unsurprisingly, DISTINCTCOUNT(). It’s optimized for this exact purpose and is very easy to use.

Here’s how to create a measure with DISTINCTCOUNT:

1. Create a New Measure: Right-click on the table you're working with in the Data pane and select New measure.
2. Write the Formula: The formula bar will appear at the top of the canvas. Type the following formula, replacing 'Sales' with your table name and 'Customer ID' with your column name:

Unique Customers = DISTINCTCOUNT(Sales[Customer ID])

1. Commit the Measure: Press Enter to save the measure. You'll see your new measure appear in the Data pane, usually with a small calculator icon next to it.

Now, you have a reusable measure called 'Unique Customers'. You can drag it into a Card, a chart, or a table, and it will always show the correct distinct count. Even better, it will recalculate automatically when you apply filters. If you filter your report to only show data for "2023", the 'Unique Customers' measure will instantly update to show only the number of customers who purchased in 2023.

An Alternative Approach: COUNTROWS and DISTINCT

Another way to achieve the same result is by combining two different DAX functions: COUNTROWS() and DISTINCT(). While DISTINCTCOUNT() is more direct, understanding this combination helps you grasp a core concept of DAX - table functions.

Here’s the logic:

• The DISTINCT(TableName[ColumnName]) function returns a new, virtual table that contains only the unique values from the column you specify.
• The COUNTROWS(TableName) function then counts the number of rows in that virtual table.

Here is what the measure looks like:

Unique Customers (_alt) = COUNTROWS(DISTINCT(Sales[Customer ID]))

For most scenarios, DISTINCTCOUNT() is slightly more optimized and the standard function to use. However, knowing this alternative pattern can be very useful when you start building more complex DAX formulas.

Handling Blanks: The DISTINCTCOUNTNOBLANK Function

Data is rarely perfectly clean. A common issue is having blank values in your columns. The standard DISTINCTCOUNT() function includes blank as one of the unique values if it exists in your column. For example, if you have customers A, B, C, and several blank entries, DISTINCTCOUNT() will return a result of 4.

If you wish to exclude blanks from your unique count, you should use the DISTINCTCOUNTNOBLANK() function.

The syntax is identical:

Unique Customers (No Blanks) = DISTINCTCOUNTNOBLANK(Sales[Customer ID])

Using the same example, this function would return 3, giving you a count of only the customers with actual recorded values.

Putting It All Together: A Practical Example

Let's see how leveraging a DAX measure makes your report interactive and insightful. Imagine you're a marketing manager and you've created the Unique Customers = DISTINCTCOUNT(Sales[Customer ID]) measure.

1. Create a KPI Card: You drag your new 'Unique Customers' measure into a Card visual to display the total number of unique customers for all time. The card shows "1,482".
2. Analyze by Category: Next, you create a bar chart. You drag 'Product Category' to the axis and 'Unique Customers' into the value field. The bar chart now shows you how many unique customers bought from each category. You might notice that "Electronics" has 850 unique customers while "Home Goods" only has 200.
3. Filter by Time: Finally, you add a Slicer visual for the 'Year'. When you click "2023" in the slicer, both the KPI Card and the bar chart instantly filter. The card now shows "634" - the unique customers for 2023 - and the bar chart adjusts to show the unique customer count per category for just that year.

This dynamic interactivity is why DAX measures are the preferred method for any serious report building. You write the logic once, and it adapts to whatever context you put it in, allowing for powerful, exploratory data analysis.

Final Thoughts

Counting unique values is a fundamental building block of insightful reporting. Whether you choose the user-friendly "Distinct count" option for a quick answer or write a flexible DAX measure with DISTINCTCOUNT, Power BI provides the tools you need to move beyond simple totals and understand the true diversity in your data.

Of course, mastering DAX and navigating a tool as deep as Power BI can still present a steep learning curve. At Graphed, we created a way to get these insights without any formulas at all. We securely connect to your marketing and sales data sources so you can simply ask questions in plain English like, "Show me a chart of unique customers by country for the last quarter." Our AI analyst builds the charts and dashboards for you in seconds, letting you focus on the insights instead of getting stuck on the technical setup.