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 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..

SUMMARIZE function 

Learn Power Bi Dax



SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 409 total views,  2 views today

Leave a Reply

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