Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Find Unique Values in Power BI

Cody Schneider••8 min read

Finding the number of unique or distinct values in your data is one of the most fundamental tasks in analysis. Whether you want to know how many unique customers made a purchase, which distinct products were sold, or the number of individual campaigns you ran last quarter, this calculation is essential. This guide will walk you through the various ways to find and count unique values in Power BI, from simple UI clicks to more flexible DAX formulas.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Counting Unique Values Matters

Before jumping into the "how," it helps to understand the "why." A simple row count tells you the volume of activity, but a distinct count tells you about the actors behind that activity. For instance, knowing you had 1,000 sales transactions is good, but knowing those sales came from only 50 unique customers tells a very different story about customer loyalty and purchase frequency.

Here are a few common scenarios where you need distinct counts:

  • Sales Analysis: Calculating the number of unique customers who purchased from you in a specific time period.
  • Marketing Analytics: Identifying the number of unique visitors to your website or a particular landing page.
  • Inventory Management: Determining the number of distinct products sold, letting you know your product catalog's breadth of performance.
  • Operations: Tracking unique support tickets to understand the scope of customer issues, rather than just the total number of interactions.

Mastering this simple calculation gives you a much clearer lens through which to view your business performance.

Method 1: The Quickest Way with "Count (Distinct)" Aggregation

The most straightforward method for finding unique values is built directly into Power BI's visualization tools. You don't need to write any code. This is perfect for quick, ad-hoc analysis directly on your canvas.

Let's say you have a sales data table and you want to quickly see how many unique customers made a purchase. You can do this in seconds using a 'Card' visual.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step-by-Step Instructions:

  1. Add a Card Visual: Go to the 'Visualizations' pane and select the 'Card' visual (the one that looks like '123'). This will add an empty card to your report canvas.
  2. Drag in Your Data Field: From the 'Data' pane, find your sales table and drag the column you want to count — in this case, 'Customer Name' — into the 'Fields' well of the card visual.
  3. Change the Aggregation: By default, Power BI might show you the first customer name alphabetically. We need to change this. With the card visual selected, go to the 'Fields' well, click the small downward arrow next to 'Customer Name', and a context menu will appear. From this menu, select 'Count (distinct)'.

That's it! The card visual will now display the exact number of unique customer names in your dataset.

This method works for almost any visual, not just cards. For example, you could add 'Count (distinct)' of Customer Name to a table or matrix visual showing sales by region to see the number of unique customers in each market.

Pros: Fast, easy, and requires no code. Cons: Less reusable than a DAX measure. You have to repeat this configuration for every visual where you need the distinct count.

Method 2: Using DAX for Reusable Measures

For more robust and reusable calculations, Data Analysis Expressions (DAX) is the way to go. Creating a DAX measure allows you to define the calculation once and then use it in any visual you want. It also gives you more control and is generally more efficient on large datasets.

There are a few DAX functions that get the job done. We'll start with the most intuitive and best-practice one.

Using the DISTINCTCOUNT Function

The DISTINCTCOUNT function is specifically designed for a single purpose: counting the number of distinct values in a column. It's concise, easy to read, and optimized for performance.

How to Create the Measure:

  1. Create a New Measure: Right-click on your primary data table (e.g., 'Sales') in the 'Data' pane and select 'New measure'.
  2. Write the DAX Formula: The DAX formula bar will appear at the top. Here, you'll define your measure. The syntax is very simple. Give your measure a descriptive name, followed by the formula:
Unique Customer Count = DISTINCTCOUNT(Sales[Customer Name])
  1. Commit the Formula: Press 'Enter' or click the checkmark to save the measure. You will now see your new measure (with a small calculator icon) in your data table in the 'Data' pane.

Now you can use this Unique Customer Count measure anywhere in your report. Drag it onto a card, add it to a table, or use it in a bar chart. It’s a permanent part of your data model, ready to go whenever you need it.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

The COUNTROWS and DISTINCT Combination

Another way to achieve the same result is by combining two functions: DISTINCT and COUNTROWS. This method is a little more verbose but helps you understand what's happening under the hood.

  • DISTINCT(ColumnName): This function scans a column and returns a temporary, single-column table containing only the unique values from that column.
  • COUNTROWS(TableName): This function then counts the number of rows in a given table (including the temporary one created by DISTINCT).

So, you're essentially creating a list of unique names first and then counting how many names are in that list.

How to Create the Measure:

Following the same steps to create a new measure, you would use this formula:

Unique Customers (COUNTROWS) = COUNTROWS(DISTINCT(Sales[Customer Name]))

While this works perfectly well, DISTINCTCOUNT is generally recommended. It performs the exact same operation but is cleaner to write and sometimes slightly more performant because it signals a single, optimized operation to the Power BI engine.

Method 3: Finding Unique Values in Power Query

Sometimes you need to find unique values during the data cleaning and preparation stage, before your data even gets loaded into the Power BI model. This is where the Power Query Editor comes in handy. This approach is useful if you want to create a new query that contains only unique values or simply to inspect your data during an ETL (Extract, Transform, Load) process.

Viewing Distinct Counts with Column Quality

Power Query has a fantastic feature that lets you quickly see the number of distinct and unique values in any column without writing formulas or transforming your data.

  1. Navigate to the 'Home' tab and click on 'Transform data' to open the Power Query Editor.
  2. Select the query (table) you want to examine from the left-hand 'Queries' pane.
  3. Go to the 'View' tab in the Power Query ribbon.
  4. In the 'Data Preview' section, check the box for 'Column quality'.

Instantly, a small info box will appear below each column header, showing you important metrics: the percentage of valid, error, and empty values, along with counts for Distinct and Unique values. 'Distinct' shows the total number of different values in the column, while 'Unique' shows the number of values that appear only once.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Creating a List of Unique Values

If your goal is to create a new, clean list of all unique entities (for example, a list of all sales representatives), you can do so directly in Power Query.

  1. In the Power Query Editor, find the table and column you're interested in.
  2. Right-click the column header (e.g., 'Sales Rep').
  3. From the context menu, select 'Remove Duplicates'.

This action will modify your current table, removing all rows except the first occurrence of each value in that column. If you don't want to alter your original source data, you should first duplicate the query (right-click the query name > 'Duplicate') and then perform the 'Remove Duplicates' step on the new query. This gives you a separate reference table containing a clean list of unique values.

Practical Advice and Best Practices

  • Use DAX Measures for Dashboards: For any metric you plan to use repeatedly in visuals (like 'Total Customers' or 'Distinct Products Sold'), always create a DAX measure using DISTINCTCOUNT. This keeps your report consistent, performant, and easy to maintain.
  • Use "Count (distinct)" for Quick Checks: The visual-level aggregation is perfect for quick, exploratory analysis when you just need a number fast and don't plan to reuse it.
  • Use Power Query for Data Modeling: Use the Power Query approach when you need to create a dedicated table of unique values (often called a 'dimension table') to build relationships in your data model.
  • Beware of Blanks: The DISTINCTCOUNT function counts blank values as a distinct value. If your column has blanks that you want to ignore, you may need a more advanced DAX formula, like CALCULATE(DISTINCTCOUNT(TableName[ColumnName]), TableName[ColumnName] <> BLANK()).

Knowing how to find unique values is a small but powerful skill in your analyst toolkit. By choosing the right method for the job, you can unlock clearer, more accurate insights from your data.

Final Thoughts

Whether you're using simple aggregations in visualizations, building robust DAX measures with DISTINCTCOUNT, or cleaning data in Power Query, Power BI offers flexible ways to count unique values. Each method has its place, from quick ad hoc checks to building a scalable and professional-grade data dashboard.

Analyzing data across all your marketing and sales tools can feel like a constant battle of exporting, cleaning, and reporting. We built Graphed to solve this by connecting all your data sources — like Google Analytics, Salesforce, and Shopify — in one place. You can build real-time dashboards and get answers instantly by asking questions in plain English, allowing you to focus on strategy instead of spending your time just wrangling data.

Related Articles