How to Get Distinct Column Values in Power BI

Finding unique values in a column is one of the most common tasks you'll tackle in data analysis. Whether you want to know how many distinct customers made a purchase or need a clean list of all the product categories you sell, working with unique values is a fundamental building block of any good report. This tutorial will walk you through several straightforward methods to get distinct column values in Power BI, from quick visual counts to more powerful data shaping techniques in Power Query and DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Do You Need Distinct Values Anyway?

Before jumping into the "how," it's helpful to understand the "why." Almost every meaningful report relies on distinct values in some way. Counting or listing unique items helps you answer key business questions and build more efficient dashboards.

Here are a few common scenarios:

• Understanding Customer Behavior: Counting the number of unique customers who bought something in a specific period tells you about your reach, separate from transaction volume. Ten sales could be from ten different customers or one customer buying ten times - a crucial distinction!
• Product & Inventory Analysis: Generating a list of distinct products sold can help you understand your product catalog's performance, identify slow-moving items, or create filters for your report.
• Sales & Marketing Metrics: You might need to know how many unique regions your sales team covered or count the distinct campaign sources driving traffic to your website.
• Data Cleaning: Extracting a list of unique values is often the first step in spotting and correcting inconsistent data entry. For example, finding "USA," "U.S.A.," and "United States" in a country column.
• Building Slicers and Filters: To create efficient dropdown menus or filters in your reports, you often need a clean, single-column table of unique values to power them.

Method 1: The Easiest Way - "Count Distinct" in a Visual

If all you need is a simple count of unique values, Power BI's built-in aggregations are fast and effortless. This is the perfect method for getting a quick KPI on a dashboard without writing a single line of code.

Let's say you have a sales table and want to display the total number of unique customers who have made a purchase.

Step-by-Step Instructions:

1. From the Visualizations pane, select a visual. The Card visual is perfect for displaying a single number like this.
2. With the visual selected, find your data table in the Data pane. Drag the column you want to count - in this case, "Customer Name" or "CustomerID" - into the 'Fields' well of the card visual.
3. By default, Power BI might show you the 'First Customer Name'. We need to change this aggregation. Click the small down-arrow next to the field name in the Fields well.
4. From the dropdown menu that appears, select Count (distinct).

That’s it! The card visual now displays the exact number of unique customers in your dataset. This works on almost any visual, whether you're creating a table, bar chart, or matrix.

Pro-Tip: Pay close attention to the difference between Count and Count (distinct). If you select 'Count', Power BI counts every single row in that column, including duplicates. 'Count (distinct)' will only count each unique value once.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Getting a List of Distinct Values with Power Query Editor

Sometimes, a count isn't enough. You need the actual list of unique values. For this, the Power Query Editor (accessed via the 'Transform Data' button in the ribbon) is your best friend. This is the "back-end" of Power BI where you clean, shape, and prepare your data before it gets loaded into your report model.

This method is ideal for creating "dimension tables" - smaller lookup tables used to build an efficient data model (like a clean list of Customers, Products, or Dates).

Option A: Creating a New Table with Unique Values

Let's create a new, separate table that contains only a list of your unique product categories. This method is non-destructive, meaning it won't alter your original sales data table.

Step-by-Step Instructions:

1. Click Transform Data from the Home tab to open the Power Query Editor.
2. In the Queries pane on the left, find your main data table (e.g., 'Sales').
3. Right-click on the 'Sales' query and select Reference. This creates a new query that is linked to your original data.
4. Rename this new query to something meaningful, like "Product Categories."
5. In the data preview for your new query, find the column you want to get distinct values from (e.g., 'Product Category'). Click to select it.
6. Go to the Transform tab in the ribbon. In the 'Any Column' group, select Remove Columns -> Remove Other Columns. This leaves you with only the one column you care about.
7. Now, with that single column still selected, head to the Home tab and click Remove Rows -> Remove Duplicates.

You'll be left with a simple, one-column table containing every unique product category. Now you can click Close & Apply in the top-left corner. This new table will be available in your data model to create relationships, slicers, and more.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Option B: Removing Duplicates Within a Column

If your goal is to reduce an entire table to only rows with unique values based on a specific column, you can do that directly within Power Query. Warning: Be careful with this approach, as it filters your entire table and might inadvertently remove important data.

In Power Query, simply right-click the header of the column you want to be unique and select Remove Duplicates. This will check that column and remove any row after the first instance of a value appears.

Method 3: Flexible Analysis with DAX Functions

DAX (Data Analysis Expressions) is the formula language of Power BI. While it has a steeper learning curve, it offers incredible flexibility for more complex analysis. You can use DAX to create dynamically calculated Measures or new Calculated Tables.

Counting Distinct Values with DISTINCTCOUNT()

The DISTINCTCOUNT() function is the DAX equivalent of the "Count (distinct)" aggregation you used in the first method. The difference is that by creating it as a reusable measure, you can use it in more complex formulas and contexts.

How to create a measure:

1. In Power BI Desktop's Report View, right-click on your data table in the Fields pane and choose New measure.
2. The formula bar will appear. Type in your formula:
3. Press Enter to save the measure.

This new measure (usually indicated with a calculator icon) now lives in your data model and can be dragged into any visual just like a regular column. This is the preferred method for any core KPI you plan to use throughout your report.

Generating a Table with VALUES() or DISTINCT()

You can also use DAX to create an entire table of unique values - similar to what we did in Power Query, but this table is generated by a formula instead of a series of transformation steps.

The two main functions for this are VALUES() and DISTINCT(). They do very similar things, but with a small key difference:

• DISTINCT('Table'[Column]): Returns a single-column table containing only the unique values from that column.
• VALUES('Table'[Column]): Also returns a single-column table of unique values, but with one difference: if there are relationship integrity issues in your model, VALUES() can also return a BLANK() row. DISTINCT() will not. For most cases, they're interchangeable, but DISTINCT is often a slightly safer bet.

To use them, go to the Modeling tab in the ribbon and select New Table. Then you would enter a formula like this:

Sales Reps = DISTINCT(Sales[Sales Representative])

This creates a virtual, callable table that can be used for slicers or advanced DAX formulas without cluttering your Power Query.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Putting It All Together: A Quick Scenario

A sales manager asks for a simple, one-page report showing:

1. The total number of unique client companies dealt with this year.
2. A filter so they can select a specific country.

Here’s how you’d use the methods above:

• For the first request, you'd create a DAX measure: Total Unique Clients = DISTINCTCOUNT(Sales[ClientName]). Then, you'd place that measure in a Card visual.
• For the country filter, a best practice is to use Power Query (Method 2) to create a new, referenced table called 'Countries'. In that table, you would remove all other columns and then remove duplicates from the 'Country' column. Back in the report view, you would use this new 'Countries' table to create a Slicer visual.

Which Method is Right for You? A Quick Cheat Sheet

With several options available, which one should you choose? It comes down to what you're trying to achieve.

• For a fast, simple count inside a single visual: Use the "Count (distinct)" aggregation (Method 1).
• For data modeling, cleaning, or building filter tables: Use the Power Query Editor to create a new, deduplicated table (Method 2).
• For a reusable, core business KPI count that you can use across your report: Create a DAX measure with DISTINCTCOUNT() (Method 3).
• For creating a virtual table of values inside an advanced DAX formula or as a calculated table: Use DAX functions like DISTINCT() or VALUES() (Method 3).

Final Thoughts

Mastering how to count and list distinct values is a surprisingly powerful skill that unlocks a deeper level of insight in Power BI. Whether you're using a quick visual aggregation, cleaning data in Power Query, or writing a reusable DAX measure, you now have a full toolset to tackle any request that comes your way.

We know that even "simple" steps in complex BI tools can require clicking through countless menus, wrestling with formulas, and referencing tutorials. We built our product specifically to eliminate this friction. Instead of manually creating tables or writing DAX, with Graphed, you connect your data sources and just ask a question in plain English. Prompting "How many unique customers did we have in Q4?" will instantly generate the right chart or KPI in a live, interactive dashboard, turning hours of report-building overhead into a quick conversation.