Contact us

Send
inqury

Knowledge base

home banner

bi@unija.com

  • Knowledge

  • Categories

November 20, 2020

DAX function VALUES, HASONEVALUE

Author: Branka Trifunović

For Power BI version 2.76.5678.782 (December 2019).

Function VALUES()

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.

Syntax

VALUES(TableOrColumnName)

Parameter Description
TableOrColumnName 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.

Function HASONEVALUE()

Returns TRUE if only one value remains after filter context. Otherwise returns FALSE:

Syntax

HASONEVALUE(ColumnName)

Parameter Description
ColumnName Column, that has one or more unique values

Example

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.

Function SELECTEDVALUE()

Returns value, if only one value is left after filter context. Otherwise returns BLANK().

Syntax

SELECTEDVALUE(ColumnName, [AlternativeResult])

Parameter Description
ColumnName Column that has one or more unique values
AlternativeResult Value returned in case more than one value is left after filter context. Default value for this parameter is BLANK(). (optional)

Example

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.

 

Index