How to Use VALUES in DAX in Power BI: Syntax and Examples
In Power BI, the
VALUES function returns a one-column table of unique values from a specified column or all unique rows from a table. It is commonly used to filter or summarize data by distinct values in DAX formulas.Syntax
The VALUES function has two main forms:
VALUES(ColumnName): Returns a one-column table of unique values from the specified column.VALUES(TableName): Returns a table of unique rows from the specified table.
This function is useful to get distinct values for filtering or calculations.
DAX
VALUES(ColumnName) VALUES(TableName)
Example
This example shows how to use VALUES to get unique product categories from a Products table and count them.
DAX
UniqueCategories = COUNTROWS(VALUES(Products[Category]))
Output
If Products[Category] has values: "Bikes", "Accessories", "Bikes", "Clothing"
Then UniqueCategories = 3
Common Pitfalls
One common mistake is using VALUES expecting it to return all rows including duplicates; it only returns unique values. Also, if the column has blanks, VALUES includes a blank row which can affect calculations.
Another pitfall is confusing VALUES with ALL. VALUES respects filters and returns distinct values in the current filter context, while ALL ignores filters.
DAX
Wrong: TotalCategories = COUNTROWS(VALUES(Products[Category])) // Respects filters Right: TotalCategories = COUNTROWS(ALL(Products[Category])) // Ignores filters
Quick Reference
VALUES Function Summary:
- Returns unique values from a column or unique rows from a table.
- Respects current filter context.
- Includes blank values if present.
- Useful for filtering, counting distinct values, and creating slicers.
Key Takeaways
VALUES returns a one-column table of unique values from a column or unique rows from a table.
It respects the current filter context and includes blank values if present.
Use VALUES to count distinct items or filter data based on unique values.
Do not confuse VALUES with ALL; VALUES respects filters, ALL ignores them.
Be aware that blank values are included and can affect your calculations.