November 20, 2020
DAX function VALUES, HASONEVALUE
For Power BI version 2.76.5678.782 (December 2019).
Function VALUES() returns a column of unique values. Function takes in initial filter context that comes from visualizations and slicers. You can find more on filter context in Filter context in Power BI.
|Column where we want to keep only unique values or table where we want to keep only unique rows
Example: unique values from a column
We want unique values from Location Name column in the Sales table.
We create new table with Modelling > New Table and enter the following formula.
VALUES_Example = VALUES(Sales[Location Name])
Example: filter context and VALUES()
Function VALUES() takes in the initial filter context coming from visualizations and slicers. Let’s look at the last example. We have sales table Sales with columns Platform and Publisher. We create Matrix visualization and insert field Platform to rows. We then create the following measure.
VALUES_Example = COUNTROWS(VALUES(Sales[Publisher]))
Function takes in the initial filter context, coming from Matrix visualization. Table Sales is filtered for each row to the appropriate Platform. Unique values in column Publisher is then counted.
Returns TRUE if only one value remains after filter context. Otherwise returns FALSE:
|Column, that has one or more unique values
We have a table Platform_measures.
We wish to check if each Platform is repeated only once. We create a measure.
HASONEVALUE_Example = HASONEVALUE(Locations_measures[Platform])
We have to create a new Matrix visualization and insert Platform to rows. Visualization filters the table Platform_measures inside every row and returns TRUE for all rows, meaning each Platform indeed only occur once.
Returns value, if only one value is left after filter context. Otherwise returns BLANK().
|Column that has one or more unique values
|Value returned in case more than one value is left after filter context. Default value for this parameter is BLANK(). (optional)
We have Platform_measures table.
We want to check if each value only shows up once and return it’s values it that’s true. We create new measure.
SELECTEDVALUE_Example = SELECTEDVALUE(Platform_measures[Platform])
Table Platform_measures is filtered by initial filter context and then filtered by each row. One row is left for each platform. Value of platform is returned.