How to Use DISTINCT in DAX in Power BI: Syntax and Examples

In Power BI, use the DISTINCT function in DAX to return a one-column table of unique values from a specified column. It helps remove duplicates and is often used in calculated tables or measures to analyze distinct entries.

Syntax

The DISTINCT function syntax is simple:

• DISTINCT(ColumnName)

Where ColumnName is the column you want unique values from. It returns a table with only distinct values from that column.

DAX

DISTINCT(TableName[ColumnName])

Example

This example shows how to create a calculated table with unique product names from a sales table.

DAX

UniqueProducts = DISTINCT(Sales[ProductName])

Output

A table with one column 'ProductName' containing each product name only once, no duplicates.

Common Pitfalls

Common mistakes when using DISTINCT include:

• Using it on multiple columns directly (it only accepts one column).
• Expecting it to return a list or scalar value instead of a table.
• Not wrapping it in aggregation functions when used in measures.

For example, DISTINCT(Sales[ProductName], Sales[Category]) is invalid because DISTINCT accepts only one column.

DAX

/* Wrong usage: */
DISTINCT(Sales[ProductName], Sales[Category])

/* Correct usage for multiple columns: */
DISTINCT(SELECTCOLUMNS(Sales, "Product", Sales[ProductName], "Category", Sales[Category]))

Quick Reference

Function	Description	Usage Example
DISTINCT	Returns unique values from one column	DISTINCT(Sales[ProductName])
SELECTCOLUMNS	Creates a table with selected columns	SELECTCOLUMNS(Sales, "Prod", Sales[ProductName])
VALUES	Returns distinct values including blanks	VALUES(Sales[ProductName])

Key Takeaways

DISTINCT returns a one-column table of unique values from the specified column.

Use DISTINCT only on a single column; for multiple columns, combine with SELECTCOLUMNS.

DISTINCT returns a table, so use aggregation functions to get scalar results in measures.

DISTINCT helps remove duplicates and is useful for filtering and summarizing data.