The VALUES function returns a table containing the different values that the column included as an argument takes in the current context including a possible Blank value, or a copy of the table included as an argument plus a possible row of Blanks.
VALUES(
TableNameOrColumnName
)
- TableNameOrColumnName: Name of a table or column whose different values you want to obtain.
The VALUES function returns a table containing the distinct values identified in the indicated column or table.
In most cases, the result returned by this function and by DISTINCT is the same, since both functions eliminate duplicate values and return the list of unique values. But VALUES can include a Blank in those scenarios in which what is known as a referential integrity violation occurs (for example, when information is being extracted from a related table but the value used in the relationship does not exist in one of the tables).
In the case where the argument passed to the function is a column, the function removes duplicate values. If it is a table, duplicates are kept.
If we have a table with sales in which we define the following measure to sum the sales in the current context:
Ventas = SUM(FactSales[SalesAmount])
...we can take the categories, subcategories and sales to a matrix, as follows:
Now we can add a field that indicates the number of subcategories being considered in each of the rows of the matrix if we define the following measure:
# = COUNTROWS(VALUES(DimProductSubcategory[ProductSubcategoryName]))
In this measure we extract the set of values that the DimProductSubcategory [ProductSubcategoryName] field takes and we count the number of values in the result. If we take this measure to the matrix, the result is the following:
We see how this measure returns 1 for all those contexts in which there is only one subcategory involved, and the number of subcategories in the calculations of the totals per category.
