Most Commonly Used 10 DAX Functions in Power BI

Yana Khare Last Updated : 17 Jul, 2024
7 min read

Introduction

Power BI uses a set of functions, operators, and constants called DAX to perform dynamic computations and analysis. One can enhance their Power BI competency by using DAX features that help in data modeling and reporting. This article examines the top DAX features that any Power BI user should know.

Top DAX Functions in Power BI

What is Power BI?

Power BI was by Microsoft. It is a business analytics software that enables clients to analyze, view, and share their data. Power BI has ‘wizards’ that are used to create interfaces that can easily manipulate the data. It also provides business intelligence and tools such as graphs and charts through which end users can generate reports and dashboards.

What are DAX Functions?

Some commonly used operators, functions, and constants used in formulae and expressions in Power BI, Power Pivot, and Analysis Services included DAX, which can be used to calculate and even return outcomes.

Among the main attributes of DAX functions are:

  • Data Aggregation: Data may be summarized using functions like SUM, AVERAGE, COUNT, and SUMX.
  • Filtering: Complex data filters may be defined using functions like FILTER and CALCULATE.
  • Time Intelligence: Date and time computations are handled by functions like DATEADD, DATEDIFF, and YEAR.
  • Lookup functions: LOOKUPVALUE and other similar functions facilitate the retrieval of relevant data from many tables.

Why is Power BI Essential for Data Analysis?

Power BI is essential for data analysis due to the following reasons:

  1. Interactive Visualizations: A vast array of dynamic and adaptable data visualizations offered by Power BI facilitate data interpretation and meaningful presentation.
  2. Real-time Data Access: It facilitates the connectivity of many data sources, including real-time data streams, so that analysis and reporting are up to date.
  3. Data Transformation and Preparation: Power BI offers an easy way to clean up, transform, and prepare data for analysis in Power Query so users can write a little code.
  4. Advanced Analytics: Power BI provides for complex calculations and rich analyses. The DAX features an AI built into the system to help the users.
  5. Integration and Collaboration: Power BI is compatible with Microsoft Excel, Azure, and SharePoint tools. It also enhances teamwork and sharing of different tasks and ideas.

Top DAX Functions in Power BI

Here are some of the most commonly used DAX functions:

  1. CALCULATE
  2. SUM and SUMX
  3. CALCULATETABLE
  4. RANKX
  5. DATEDIFF
  6. DATEADD
  7. COUNT and COUNTROWS
  8. LOOKUPVALUE
  9. FILTER
  10. RELATED

Below is a sample dataset of the Sales and Products tables that can be used to perform the DAX functions mentioned above.

Sales Table

OrderIDDateProductIDQuantityUnitPriceCustomerIDSalesPersonID
12023-01-01P001510.99C001SP01
22023-01-02P002315.5C002SP02
32023-01-03P003220C003SP01
42023-01-04P004150C004SP03
52023-01-05P005412.99C005SP02
62023-01-06P006225C006SP01
72023-01-07P007318.99C007SP03
82023-01-08P008175C008SP02
92023-01-09P00959.99C009SP01
102023-01-10P010230C010SP03
112023-01-11P001310.99C011SP02
122023-01-12P002215.5C012SP01
132023-01-13P003420C013SP03
142023-01-14P004150C014SP02
152023-01-15P005312.99C015SP01
162023-01-16P006225C016SP03
172023-01-17P007118.99C017SP02
182023-01-18P008275C018SP01
192023-01-19P00949.99C019SP03
202023-01-20P010330C020SP02
212023-01-21P001210.99C001SP01
222023-01-22P002115.5C002SP03
232023-01-23P003320C003SP02
242023-01-24P004250C004SP01
252023-01-25P005112.99C005SP03
262023-01-26P006425C006SP02
272023-01-27P007218.99C007SP01
282023-01-28P008175C008SP03
292023-01-29P00939.99C009SP02
302023-01-30P010230C010SP01

Products Table

ProductIDProductNameCategoryCostLaunchDate
P001Premium WidgetElectronics8.52022-01-15
P002Deluxe GadgetHome122022-02-01
P003Super ToolHardware15.52022-03-10
P004Luxury DeviceElectronics402022-04-05
P005Economy ApplianceHome102022-05-20
P006Pro GizmoElectronics202022-06-15
P007Smart DoohickeyHome14.52022-07-01
P008Mega MachineHardware602022-08-10
P009Basic WidgetElectronics7.52022-09-05
P010Advanced GadgetHome252022-10-20

1. CALCULATE

The CALCULATE function is among the strongest DAX formula. It evaluates an expression in the context of a changed filter. With CALCULATE, you may alter the environment in which data is filtered, enabling more adaptable and dynamic computations.

Syntax:

CALCULATE(<expression>, <filter1>, <filter2>...)

Example:

Electronics Sales = 
CALCULATE(
    SUM(Sales[Quantity]),
    Products[Category] = "Electronics"
)
Calculate power bi dax function

Explanation:

This measure calculates the total sales for electronics products only.

  • The expression SUM(Sales[Quantity] * Sales[UnitPrice]) calculates the total sales.
  • The filter Products[Category] = "Electronics" modifies the context to include only products in the Electronics category.
  • CALCULATE applies this filter, overriding any existing filters on the Products table.

2. SUM and SUMX

The SUM function is straightforward, summing up all values in a column. SUMX, on the other hand, is an iterator function that sums up the results of an expression evaluated for each row in a table.

Syntax:

SUM(<column>)
SUMX(<table>, <expression>)

Example:

Total Quantity Sold = SUM(Sales[Quantity])

Total Profit = 
SUMX(
    Sales,
    Sales[Quantity] * (Sales[UnitPrice] - RELATED(Products[Cost]))
)
Sum DAX function
SUMX DAX function

Explanation:

  • SUM operates on a single column or on an expression that results in a column of values. It adds up all the values in that column, respecting any filter context that’s in place. It’s efficient for simple summations but limited when you need row-by-row calculations involving multiple columns or related tables.
  • SUMX iterates over each row in the specified table (Sales in this case). For each row, it evaluates the given expression: Sales[Quantity] * (Sales[UnitPrice] - RELATED(Products[Cost]))
    • It multiplies the quantity sold by the difference between the unit price and the product cost.
    • RELATED(Products[Cost]) brings in the cost from the Products table for each product.
  • After calculating this for each row, SUMX sums up all the results.

3. CALCULATETABLE

CALCULATETABLE evaluates a table expression in a modified filter context, similar to how CALCULATE works with scalar expressions.

Syntax:

CALCULATETABLE(<table_expression>, <filter1>, <filter2>,...)

Example:

Electronics_Sales =
CALCULATETABLE(
Sales,
Products[Category] = “Electronics”
)

CALCULATETABLE DAX function

Explanation:

  • It starts with the entire Sales table.
  • It then applies the filter Products[Category] = "Electronics".
  • This filter is applied through the relationship between Sales and Products tables. The result is a subset of the Sales table containing only sales of electronic products.
  • This function is handy for creating virtual tables filtered according to specific criteria.

4. RANKX

RANKX ranks items in a table based on an expression. It helps create rankings within a data set.

Syntax:

Rankx(<table>,<expression>,<value>(optional),<order>(optional),<ties>(optional))

Example:

RANKX(
    ALL(Sales[SalesPersonID]),
    CALCULATE(SUM(Sales[Quantity] * Sales[UnitPrice]))
)

Explanation:

  • The expression CALCULATE(SUM(Sales[Quantity] * Sales[UnitPrice])) calculates the total sales amount for each salesperson.
  • RANKX evaluates the expression for each row in the specified table.
  • It then assigns a rank based on the result of that expression.
  • By default, RANKX assigns a lower rank (closer to 1) for higher values.

In Power BI, RANKX helps in generating rankings dynamically within reports.

5. DATEDIFF

The DATEDIFF function calculates the difference between two dates.

Syntax:

Duration = DATEDIFF(StartDate, EndDate, DAY)

Example

Days Since Launch = 
DATEDIFF(
    Products[LaunchDate],
    MAX(Sales[Date]),
    DAY
)
DATEDIFF DAX function

Explanation:

This measure calculates the number of days between each product’s launch date and the latest sale date.

  • Products[LaunchDate] is the start date.
  • MAX(Sales[Date]) finds the latest sale date in the current context.
  • DAY specifies that the difference should be calculated in days.

In Power BI, DATEDIFF helps calculate the time elapsed between two dates in various units (days, months, years).

6. DATEADD

DATEADD shifts a date by a specified number of intervals. It helps create comparisons over time.

Syntax:

DATEADD(<dates>, <number_of_intervals>, <interval>)

Example:

NewLaunchDate = DATEADD(Products[LaunchDate], 1, MONTH)

Explanation:

In Power BI, DATEADD enables time intelligence operations, such as comparing the current month’s data to the previous month’s.

7. COUNT and COUNTROWS

While COUNTROWS counts the number of rows in a table, COUNT counts the number of non-empty values in a column.

Example:

CustomerCount = COUNT(Sales[CustomerID])
OrderCount = COUNTROWS(Sales)

Explanation:

  • COUNT on CustomerID column: This will count the number of non-blank entries in the CustomerID column.
  • COUNTROWS on the entire Sales table: This will count the total number of rows in the table.

In Power BI, these functions help determine the size of a dataset or subset.

8. LOOKUPVALUE

LOOKUPVALUE retrieves the value of a column in a table, akin to a VLOOKUP in Excel.

Syntax:

LOOKUPVALUE(<result_column>, <search_column1>, <search_value1> [, <search_column2>, <search_value2> [, … ] ] )

Example:

ProductCost = LOOKUPVALUE(Products[Cost], Products[ProductID], "P003")

Explanation:

  • result_column: The column from which you want to return the value.
  • search_column1: The column where you search for the value.
  • search_value1: The value you want to find in the search_column1.
  • This formula searches the Products table for the ProductID “P003” and returns the corresponding Cost.

In Power BI, LOOKUPVALUE is essential for fetching related data from different tables.

9. FILTER

The FILTER function returns a table representing a subset of another table filtered by a given expression.

Syntax:

FILTER(<table>,<filter>)

Example:

FilteredProducts = FILTER(
    Products,
    Products[Category] = "Electronics" && Products[Cost] > 10
)

Explanation:

This condition consists of two parts combined with the logical AND operator (&&):

  • Products[Category] = "Electronics": This part of the condition filters the rows where the Category is “Electronics”.
  • Products[Cost] > 10: This part of the condition further filters those rows where the Cost is greater than 10.

In Power BI, FILTER is used to narrow down data based on specific conditions.

The RELATED function fetches a related value from another table, leveraging the relationships between tables.

Syntax:

RELATED(<Column>)

Example:

CategoryDescription = RELATED(Categories[Description])

Explanation:

This DAX expression fetches the Description from the Categories table for each row in the Products table based on the relationship defined between the Category columns of both tables.

In Power BI, RELATED is crucial for retrieving data from related tables.

Conclusion

Proficiency with these Power BI DAX functions will significantly improve your data analysis and modeling capacity. Understanding and efficiently using functions like CALCULATE, SUMX, RANKX, DATEDIFF, and LOOKUPVALUE will enable you to perform intricate computations and produce dynamic, informative results. The DAX functions in Power BI provide the tools to transform unstructured data into insightful information, whether doing total calculations, rating data, or working with date values.

If you want to learn about DAX for Power BI in-depth, here is a YouTube tutorial for you: Hands-on with Data Analysis Expressions (DAX) for Power BI

Frequently Asked Questions

Q1. How many functions are there in DAX?

A. DAX includes over 250 functions. These functions are used for various data manipulations, including aggregation, filtering, time intelligence, and more, to perform complex calculations in Power BI, Power Pivot, and Analysis Services.

Q2. What is the DAX formula in Power BI?

A. A DAX formula is an expression used to perform calculations on data in Power BI. It consists of functions, operators, and constants to create dynamic calculations and aggregations, such as CALCULATE(SUM(Sales[Amount]), Sales[Region] = “West”).

Q3. What are DAX queries in Power BI?

A. Expressions written in DAX language retrieve, filter, and analyze data in Power BI. Users create custom calculations, aggregations, and query tables or columns, providing powerful data manipulation capabilities beyond standard report-building.

Q4. What is the full form of DAX?

A. The full form of DAX is Data Analysis Expressions.

A 23-year-old, pursuing her Master's in English, an avid reader, and a melophile. My all-time favorite quote is by Albus Dumbledore - "Happiness can be found even in the darkest of times if one remembers to turn on the light."

Responses From Readers

Clear

Eusebio Kickel
Eusebio Kickel

You have a way of making even the most challenging topics seem approachable and understandable.

Congratulations, You Did It!
Well Done on Completing Your Learning Journey. Stay curious and keep exploring!

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details