How to Show Only Distinct Values in Power BI

Counting your total sales is helpful, but knowing how many unique customers drove those sales is a far more powerful insight. Getting to those distinct, or unique, values is a common task in data analysis, and Power BI offers several ways to do it. This tutorial walks you through everything from simple one-click operations to more flexible DAX formulas for showing and counting distinct values in your reports.

Why Distinct Values Matter

Imagine you have a spreadsheet of 1,000 sales transactions from the last month. A simple 'count of rows' tells you that you made 1,000 sales. That's a good start. But what if you find that those 1,000 sales were made by only 75 unique customers? This changes the story completely.

This is the difference between total counts and distinct counts:

• Total Count (or Count): Simply counts all the rows in your data. In our example, it's 1,000 sales transactions.
• Distinct Count (or Unique Count): Counts only the unique instances of a value. In our example, it's the 75 individual customers.

Looking at distinct values helps you answer critical business questions like:

• How many individual products were sold, not just the total quantity?
• How many unique visitors came to our website, not just total page sessions?
• Which specific marketing channels brought in at least one new lead?

Working with distinct values is fundamental to understanding customer behavior, product performance, and marketing effectiveness. Now, let’s see how to do it in Power BI.

Method 1: The Quickest Way with Summarization

For a quick and easy distinct count without writing any code, you can use Power BI's built-in summarization feature directly within a visual. This is perfect when you just need a single number, like on a Card visual.

Let's find the number of unique products sold. For this, we'll assume you have a 'Sales' table with a 'Product Name' column.

Step-by-Step Guide:

1. Select a Visual: From the Visualizations pane, select the Card visual. This creates an empty card on your report canvas.
2. Add Your Data: In the Fields pane, find your 'Sales' table and drag the 'Product Name' column into the "Fields" area of the Card visual.
3. Power BI might show you the first product name alphabetically by default. This is not what we want. We need to change how Power BI summarizes this data.
4. Change the Summarization: Look at the "Fields" area in the Visualizations pane where you just dropped 'Product Name'. Click the small dropdown arrow next to the field name. A menu will appear.
5. Select Count (Distinct): From this menu, choose Count (Distinct).

Instantly, the Card visual will update to show you the exact number of unique product names in your sales data. You can use this same trick with almost any visual, like bar charts or line graphs, by adjusting the summarization on the fields you place in the Y-axis or Values wells.

Method 2: Showing a List of Unique Values in a Table

Sometimes you don't want a count, you want to see the actual list of unique values. For example, maybe you want to display all the different countries your customers are from. A simple Table visual handles this automatically.

Behind the scenes, when you add a text-based column to a visual, Power BI groups the data by the distinct values in that column. So, creating a distinct list is simpler than you might think.

Step-by-Step Guide:

1. Add a Table Visual: Select the Table visual from the Visualizations pane.
2. Drag in Your Column: Go to your Data pane, find the table containing the information you need (e.g., 'Customers' table), and drag the 'Country' column into the "Values" field well for the table.

That's it! The table on your canvas will automatically display a single, alphabetized list of every unique country found in your 'Country' column. No duplicates. This happens because the "context" of a table row is a unique combination of its dimension values. Since we've only provided one ("Country"), it shows each unique value as a separate row.

Method 3: Creating Powerful and Reusable Distinct Counts with DAX

While the click-to-summarize method is fast, what if you need to use your distinct count in other calculations? For instance, what if you want to calculate the average number of sales per unique customer? For that, you need a DAX (Data Analysis Expressions) measure.

Measures are reusable formulas that you can use across multiple visuals in your report. The go-to function here is DISTINCTCOUNT().

Using the DISTINCTCOUNT Function

The DISTINCTCOUNT() function does exactly what its name suggests: it counts the number of distinct values in a specified column.

Step-by-Step Guide to Create a Measure:

1. Create a New Measure: Right-click on your data table (let's say it's the 'Sales' table) in the Fields pane and select New Measure. This will open the formula bar at the top of the screen.
2. Write the DAX Formula: In the formula bar, type the following expression. The first part ('Unique Customer Count') is the name you are giving your new measure.

Unique Customer Count = DISTINCTCOUNT(Sales[Customer ID])

• Unique Customer Count =: This is the name for our new measure.
• DISTINCTCOUNT(): This is the DAX function we are using.
• Sales[Customer ID]: This tells the function which column to perform the distinct count on. You use the format TableName[ColumnName].

1. Confirm the Measure: Hit Enter or click the checkmark to save the measure. You'll now see it in your 'Sales' table in the Data pane, denoted by a calculator icon.

Now, you have a reusable measure! You can drag 'Unique Customer Count' into a Card visual, use it as a value in a bar chart to see unique customers by month, or use it in another measure. For example, to find average sales per customer, you could create another measure like:

Avg Sales Per Customer = DIVIDE(COUNTROWS(Sales), [Unique Customer Count])

This kind of flexibility is why learning a little bit of DAX can dramatically enhance your Power BI reports.

What about VALUES() and DISTINCT()?

When you get deeper into DAX, you'll encounter two similar functions, VALUES() and DISTINCT(). Unlike DISTINCTCOUNT(), these functions don't return a single number. Instead, they return a single-column table of unique values. This is incredibly useful for more advanced calculations or for creating dynamic slicers.

• DISTINCT('TableName'[ColumnName]): Returns a single-column table containing only the unique values from that column.
• VALUES('TableName'[ColumnName]): Almost identical to DISTINCT, but with one key difference: if there's an integrity issue (e.g., a Sale record doesn't have a matching Customer in the Customer table), VALUES will include a BLANK row in its returned table. DISTINCT will not. For most simple cases, they behave the same.

A common use case is building another measure that counts how many products have had sales: CALCULATE(DISTINCTCOUNT(Sales[ProductKey]), VALUES(Products[ProductName])). We won't go deep into that here, but it's good to know they exist for when you need them down the line.

Method 4: Handling Duplicates at the Source with Power Query

The methods above are for analyzing data already loaded into Power BI. But what if you want to fundamentally reshape your data before it ever reaches your reports? This is where Power Query comes in. Power Query is Power BI’s data transformation engine, and it’s the best place to handle duplicates if you want them gone for good.

A great use case for this is creating a "dimension table" from a "fact table." Imagine your main Sales table (a fact table) has columns for 'Product ID', 'Product Name', and 'Product Category', and the product information is repeated on every single sales line. You can use Power Query to create a clean, separate 'Products' reference table with only unique products.

Step-by-Step Guide:

1. Open Power Query: In the Home tab of Power BI Desktop, click on Transform data. This opens the Power Query Editor.
2. Duplicate Your Table: You don't want to modify your original Sales data. So, in the Queries pane on the left, right-click your 'Sales' query and select Duplicate. Let's rename this new query to "Products".
3. Choose the Relevant Columns: With the new "Products" query selected, click the Choose Columns button in the Home ribbon. Select only the columns related to the product: 'Product ID', 'Product Name', and 'Product Category'. Click OK.
4. Remove Duplicate Rows: Now for the key step. Select all the columns (click the first, hold Shift, click the last). With all columns highlighted, go to the Home tab, click the Remove Rows dropdown, and select Remove Duplicates.

Power Query will remove any rows where the combination of 'Product ID', 'Product Name', and 'Product Category' is identical, leaving you with a perfectly clean table of your unique products. Now you can click Close & Apply in the top-left to load this new table into your model. It can then be used to create relationships, slicers, and cleaner data models.

Final Thoughts

Showing distinct values in Power BI is a core skill for any analyst. As you've seen, you can go from a simple, two-click distinct count on a card visual to creating clean, unique tables in Power Query, with powerful DAX measures acting as the flexible middle ground. Choose the method that best fits your specific need - whether it’s a quick answer or a permanent, robust change to your data model.

Moving from manual data analysis in spreadsheets to powerful BI tools can feel like a big leap, but it doesn’t have to be. We built Graphed to remove this complexity entirely. Instead of learning DAX or navigating menus, you can simply connect your data sources and ask questions in plain English, like "show me the distinct count of customers from Canada last month." We turn your questions into live, interactive dashboards automatically, freeing you up to focus on insights, not setups.