VALUES Function (DAX)

How does the VALUES function (DAX) work?

The VALUES function (DAX) returns a one-column table that contains the distinct values from the specified table or column. In other words, duplicate values are removed and only unique values are returned.

VALUES Formula Syntax

VALUES(
     <TableNameorColumnName>
)

How do you use the VALUES function?

This function cannot be used to return values into a cell or column on a worksheet; rather, you use it as an intermediate function, nested in a formula, to get a list of distinct values that can be counted, or used to filter or sum other values.

Related Blog Posts

Related Support Forum Posts

Selected Valued in Cashflow – Not getting the Values Automatically
Comparing values of current period and prior period where the current period value is blank
Replace NULL values to another value in table – Power BI

Considerations when using the VALUES function

When you use the VALUES function in a context that has been filtered, such as in a PivotTable, the unique values returned by VALUES are affected by the filter. For example, if you filter by Region, and return a list of the values for City, the list will include only those cities in the regions permitted by the filter. To return all of the cities, regardless of existing filters, you must use the ALL function to remove filters from the table. The second example demonstrates use of ALL with VALUES.

In most scenarios, when the argument is a column name, the results of the VALUES function are identical to those of the DISTINCT function. Both functions remove duplicates and return a list of the possible values in the specified column. However, the VALUES function can also return a blank value. This blank value is useful in cases where you are looking up distinct values from a related table, but a value used in the relationship is missing from one table. In database terminology, this is termed a violation of referential integrity. Such mismatches in data can occur when one table is being updated and the related table is not.

When the argument is a table name, the result of the VALUES function returns all rows in the specified table plus a blank row, if there is a violation of referential integrity. The DISTINCT function removes duplicate rows and returns unique rows in the specified table.

Related Video Tutorials

Grouping & Segmenting Your Data With DAX Measure Logic - Advanced DAX

Formula examples using the VALUES function

=COUNTROWS(VALUES(‘InternetSales_USD'[SalesOrderNumber]))

= COUNTROWS(VALUES(City[CityName]))

= IF( // if there is one city for the current // filter context … COUNTROWS(VALUES(City[CityName])) = 1, // … shows the city’s name VALUES(City[CityName]), // Otherwise, show a message “More than one city” )

Related DAX Functions

FILTER Function (DAX)

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership