VALUES

VALUES

How to Use the Power BI DAX function VALUES

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.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: