VALUES and DISTINCT in Power BI - Step-by-Step Guide

Introduction

This feature helps you get a list of unique values from a column in your data. It solves the problem of duplicates when you want to see or use only distinct items in your reports or calculations.

When you want to create a slicer that shows only unique product names without repeats.

When you need to count how many different customers bought something.

When you want to filter a table to show only one row per category.

When you want to create a measure that works with unique dates in your sales data.

When you want to remove duplicate entries from a list before using it in calculations.

Steps

Step 1: Open

- Power BI Desktop

The main report view with your data model is visible

Step 2: Click

- Modeling tab

Modeling options appear in the ribbon

Step 3: Click

- New measure

A formula bar opens to write a DAX expression

Step 4: Type

- formula bar

You enter a DAX formula using VALUES or DISTINCT to get unique values from a column

💡 For example, type: UniqueProducts = DISTINCT('Sales'[ProductName])

Step 5: Press

- Enter key

The measure is created and added to the Fields pane

Step 6: Add

- a table visual on the report canvas

The table shows the unique values from the column you selected

Before vs After

Before

A table visual shows 100 rows with repeated product names like 'Apple' appearing 10 times

After

The table visual shows 15 rows with each product name listed only once, no repeats

Settings Reference

VALUES function

📍 DAX formula bar

Returns a one-column table of unique values from the specified column, including blanks if present

Default: No default, you must specify a column

DISTINCT function

📍 DAX formula bar

Returns a one-column table of unique values from the specified column, including blanks if present

Default: No default, you must specify a column

Common Mistakes

Using VALUES when you want to exclude blanks but blanks remain

VALUES includes blank values if they exist in the column

Use DISTINCT if you want unique values excluding blanks or add a filter to remove blanks

Trying to use VALUES or DISTINCT on a table instead of a column

Both functions require a single column as input, not a whole table

Specify the column inside the function, like VALUES('Table'[Column])

Summary

VALUES and DISTINCT help you get unique values from a column in your data.

VALUES includes blanks, DISTINCT returns unique values including blanks.

Use these functions in measures or calculated tables to clean or summarize data.