VALUES
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
In this article, we will explore how to use the VALUES function in Power BI DAX and the various scenarios where it can be helpful.
Syntax of VALUES Function
The syntax of the VALUES function is as follows:
VALUES(❰ColumnName❱)
Where `❰ColumnName❱` is the name of the column for which you want to retrieve the unique values.
Example Usage of VALUES Function
Let us consider an example to understand the usage of the VALUES function.
Suppose you have a dataset of sales transactions, where each row represents a single purchase made by a customer. The dataset has columns such as Product, Date, Customer Name, and Sales Amount.
If you want to retrieve the unique values of the Product column, you can use the following DAX formula:
PRODUCTS = VALUES(‘Sales Transactions'[Product])
This formula will create a new table called PRODUCTS, which will contain the unique values of the Product column.
Using VALUES Function with Other Functions
The VALUES function can be used in conjunction with other DAX functions to perform various calculations and analysis.
Sum of Sales Amount by Product
Suppose you want to calculate the total sales amount for each product. You can use the following DAX formula:
SUM_Sales = SUM(‘Sales Transactions'[Sales Amount])
This formula will calculate the total sales amount for each row in the dataset.
To retrieve the unique values of the Product column and calculate the total sales amount for each product, you can combine the VALUES and SUM functions as follows:
Sales_by_Product =
SUMX(
VALUES(‘Sales Transactions'[Product]),
[SUM_Sales]
)
This formula will create a new table called Sales_by_Product, which will contain the unique values of the Product column and the total sales amount for each product.
Average Sales Amount by Customer
Suppose you want to calculate the average sales amount for each customer. You can use the following DAX formula:
AVG_Sales = AVERAGE(‘Sales Transactions'[Sales Amount])
This formula will calculate the average sales amount for each row in the dataset.
To retrieve the unique values of the Customer Name column and calculate the average sales amount for each customer, you can combine the VALUES and AVERAGE functions as follows:
Sales_by_Customer =
AVERAGEX(
VALUES(‘Sales Transactions'[Customer Name]),
[AVG_Sales]
)
This formula will create a new table called Sales_by_Customer, which will contain the unique values of the Customer Name column and the average sales amount for each customer.
Using VALUES Function for Filtering
The VALUES function can be used for filtering data based on unique values.
Filter by Product
Suppose you want to filter the dataset to show only the sales transactions for a particular product. You can use the following DAX formula:
FILTER_Product =
FILTER(
‘Sales Transactions’,
‘Sales Transactions'[Product] = “Product A”
)
This formula will filter the dataset to show only the rows where the Product column is equal to "Product A".
To retrieve the unique values of the Product column and filter the dataset based on the selected product, you can combine the VALUES and FILTER functions as follows:
Sales_by_Selected_Product =
CALCULATE(
[SUM_Sales],
FILTER(
VALUES(‘Sales Transactions'[Product]),
‘Sales Transactions'[Product] = “Product A”
)
)
This formula will create a new table called Sales_by_Selected_Product, which will contain the unique values of the Product column and the total sales amount for the selected product.
The VALUES function in Power BI DAX is a powerful tool that can help you to retrieve the unique values in a column. This function can be used in various scenarios, such as filtering, grouping, and calculating data based on distinct values.
In this article, we explored the syntax of the VALUES function and its various usage scenarios. We learned how to use the VALUES function with other DAX functions to perform calculations and analysis, and how to use it for filtering data based on unique values.
By using the VALUES function effectively, you can gain valuable insights into your data and make more informed business decisions.
