VALUES
A column's distinct values in the filter context, or a table's rows, with the blank row a relationship adds for values the other table lacks.
Group: Filter. Compatible with DAX.
Syntax
VALUES ( <TableNameOrColumnName> )| Parameter | Type | Description |
|---|---|---|
TableNameOrColumnName | Column or table | A column of the model, or a table of the model. |
Returns
Table. The column's different values, or the table's rows, that the filter context leaves.
Remarks
- When the many side of a relationship holds keys the one side lacks, the one side's table gets a blank row for them, so their numbers can be shown. VALUES includes it;
DISTINCTleaves it out. - Where one value is expected, a VALUES of one row is taken as that value: in a row for IPA,
VALUES ( Products[Category] )is Beer. With more than one row it is an error; guard it withHASONEVALUE, or useSELECTEDVALUE. - Give it a column or a table of the model. For the different rows of a table expression, use DISTINCT.
Examples
The categories
EVALUATE
VALUES ( Products[Category] )
ORDER BY Products[Category]| Products[Category] |
|---|
| Beer |
| Cider |
One value taken as a value
EVALUATE
SUMMARIZECOLUMNS (
Products[Product],
"Category", VALUES ( Products[Category] )
)
ORDER BY Products[Product]| Products[Product] | Category |
|---|---|
| Cider | Cider |
| IPA | Beer |
| Lettøl | Beer |
| Pils | Beer |
Counting what the filter leaves
The depot sold nothing, so no shop of Sales is left in its row; Places still has the depot itself.
EVALUATE
SUMMARIZECOLUMNS (
Places[Kind],
"Shops in Sales", COUNTROWS ( VALUES ( Sales[Shop] ) ),
"Places", COUNTROWS ( VALUES ( Places[Place] ) )
)
ORDER BY Places[Kind]| Places[Kind] | Shops in Sales | Places |
|---|---|---|
| Depot | (blank) | 1 |
| Shop | 15 | 15 |
Related functions
DISTINCT, SELECTEDVALUE, HASONEVALUE, FILTERS, ALL, COUNTROWS.