DAX DISTINCT Function: Remove Duplicate Rows and Get Unique Values

What Is DISTINCT in DAX?

DISTINCT is a DAX function that returns a table containing
unique values from a column or unique rows from a table. It removes
duplicate values or duplicate rows from the result.

The function can be used with a column or a table, depending on the
result you want to retrieve.

Basic Syntax

DISTINCT(<Column>)

Or:

DISTINCT(<Table>)

1. Retrieving the Complete Product Table

Query

EVALUATE
    'Product'

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of a table
expression.

Step 2: ‘Product’
‘Product’ is the table expression. Since we are passing
the entire table, DAX returns all rows and columns from the Product table.

What will you get?

The result contains the complete Product table, including all available
columns and rows.

The example above is only for illustration. Your actual result will
contain the data available in your Product table.

2. Getting the Product Category List Using DISTINCT

Query

EVALUATE
    DISTINCT('Product'[Category])

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.

Step 2: DISTINCT
DISTINCT removes duplicate values from the specified
column and returns only unique values.

Step 3: ‘Product'[Category]
This is the column from which we want to retrieve unique category values.

What will you get?

If the Product table contains repeated category values, the result will
contain each category only once.

For example, if the Product table contains the category
Components in 100 different rows, the DISTINCT query
returns Components only once.

3. Removing Duplicate Rows from the Product Table

Query

EVALUATE
    DISTINCT('Product')

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.

Step 2: DISTINCT
DISTINCT removes duplicate rows from the table expression.

Step 3: ‘Product’
The entire Product table is passed to DISTINCT. DAX compares the complete
row values and returns only unique rows.

What will you get?

The result contains the Product table without duplicate rows.

If two rows contain exactly the same values in every column, DISTINCT
returns only one of those rows.

Understanding the Difference Between DISTINCT Column and DISTINCT Table

How DISTINCT Works with a Column

Consider the following category values in the Product table:

When we execute:

EVALUATE
    DISTINCT('Product'[Category])

DAX returns:

Notice that the repeated category values have been removed.

How DISTINCT Works with a Table

When DISTINCT is applied to a table, DAX compares the complete row values.

For example, consider these rows:

The first two rows are identical across all columns. Therefore:

EVALUATE
    DISTINCT('Product')

Returns:

The duplicate row has been removed.

Important Notes

1. DISTINCT returns a table

The result of DISTINCT is always a table expression. It can be used with
EVALUATE to display the result in a DAX query.

2. DISTINCT on a column returns unique values

When you pass a column to DISTINCT, the result contains only unique values
from that column.

3. DISTINCT on a table returns unique rows

When you pass a table to DISTINCT, the result contains unique rows based on
the complete set of columns in the table expression.

4. DISTINCT does not modify the original table

DISTINCT creates a new table expression for the query result. It does not
delete or change rows in the original Product table.

5. DISTINCT can be used with other table functions

DISTINCT is commonly used with functions such as
FILTER, SELECTCOLUMNS, and
SUMMARIZE to create unique and filtered table results.

EVALUATE
    DISTINCT('Product'[Color])

EVALUATE
    DISTINCT('Product')

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist