VALUES
Returns the distinct values, or the distinct rows, that are visible in the current filter context.
VALUES(<table> | <column>)Returns: A one-column table for a column, or the table of visible rows. May include a blank row for unmatched keys.
What it does
VALUES returns the unique values of a column that survive the current filters. In a visual with one row per store, VALUES(Stores[StoreName]) returns the store on that row. It is the standard way to count members, loop over the members a user can see, or find out what the user selected.
Syntax and arguments
- table or column: a column gives a one-column table of distinct values. A table gives its distinct visible rows.
- VALUES can return a blank row when the relationship has keys with no matching dimension row. Counts that use VALUES may be one higher than the number of real members.
Example
The number of stores the user can currently see.
Visible Stores =
COUNTROWS ( VALUES ( Stores[StoreName] ) )
In a card, the figure counts the stores the slicers allow. In a matrix row for one store, it returns 1.
Example: a second use
Label a card with the single channel selected, or with a message when several are selected.
Channel Label =
IF (
HASONEVALUE ( Channel[ChannelName] ),
VALUES ( Channel[ChannelName] ),
"All selected channels"
)
HASONEVALUE checks that exactly one channel is visible. Only then is VALUES used as a single value, which DAX allows for a one-row table.
Things to watch
- Used as a single value,
VALUESmust return one row. With several values the result is an error, so test withHASONEVALUEor useSELECTEDVALUEfirst. VALUESfollows the slicers, so it shows the selection.ALLreturns the full list.- A count from
VALUEScan include the blank row.DISTINCTdoes not add it. - Measures inside an iterator over
VALUESare evaluated once per member, which is useful and can be slow on long lists. VALUESis the usual argument forTREATASandCALCULATEfilters that copy a selection to another column.
In business terms
Counting active customers or vendors in a period, listing the accounts selected for a commentary, building the list of periods in a variance report, and checking whether one entity is selected before showing its detail.