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 purposely 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 verify 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.
Also Read..
