Home » VALUES function DAX

VALUES function DAX

VALUES Function DAX




VALUES function is a Power Bi table manipulation function in DAX which removes duplicate values and return unique values. A blank values can be added.

SYNTAX

VALUES(<TableNameOrColumnName>)

TableName or ColumnName is column from which unique values are to be returned, or a table from which rows are to be returned.

When you pass input parameter as column name, It returns a one-column table which contains unique values from the given column. A BLANK value can be added.

When you pass the input parameter as a table name, It returns the rows from the specified table. Duplicate rows are preserved. A BLANK row can be added.

Lets look at an example of using VALUES function in Power Bi.

You can see a Dataset TblA having a column Name that contains duplicate values which are taken purposuly to see the ability of VALUES function on removing the duplicate values.

DataSet – TblA

 

Lets see how VALUES functions removes duplicate values for a specified column.

Go to Ribbon Bar, Click on Modeling and select create a new table.

 

Now we write a DAX that create a new table TblB which uses a VALUES function that returns a unique values for column Name from Dataset  TblA.

TblB = VALUES(TblA[Name])

 

Once you commit a DAX, It creates a new table tblB that will have a unique Name values.

 

 

 

 

 

 

 

To verfiy the DAX output you can, check it in DATA view, there you can see exact number of rows  are returned by DAX.

Take a look at below given screenshot, there we have unique name value including blank.

You can also check it using measure, We will create a Measure that uses VALUES function and returns unique row count for TblA.

Lets create a DAX measure inside Dataset tblA, that counts a unique rows in tblA  using a VALUES function.

Get_UniqueValues = COUNTROWS(VALUES(TblA[Name]))

 

Lets commit the DAX, and drag measure Get_UniqueValues into card visual to see the unique values in tblA.

You can see, it returns unique name values that is 6.

 

SUMMARIZE function 

Learn Power Bi Dax



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.