Microsoft Learn

Introduction to DAX

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. It's designed for working with data stored in tabular models and allows you to perform complex calculations and data manipulations.

This tutorial will guide you through the fundamental concepts of DAX, providing you with the building blocks to write your own formulas and unlock the full potential of your data models.

Why Learn DAX?

• Create powerful calculations for business intelligence.
• Enhance reporting and dashboarding capabilities.
• Gain deeper insights into your data.
• Become more proficient with tools like Power BI and Excel Power Pivot.

Key Concepts Covered:

• Syntax: Understanding the basic structure of DAX formulas.
• Functions: Exploring common DAX functions for aggregation, filtering, and logic.
• Context: Grasping the concept of row context and filter context.
• Calculated Columns vs. Measures: Differentiating between these two crucial elements.

Understanding DAX Syntax

DAX formulas typically start with an equals sign (=) and consist of one or more functions, along with arguments.

The basic syntax looks like this:

=FunctionName(Argument1, Argument2, ...)

For example, a simple sum calculation:

=SUM(Sales[SalesAmount])

Example: Creating a Calculated Column

Let's say we want to calculate the profit for each sale. We can add a calculated column to our 'Sales' table:

Profit = Sales[SalesAmount] - Sales[CostAmount]

This formula subtracts the `CostAmount` from the `SalesAmount` for each row in the `Sales` table, creating a new column named `Profit`.

Common DAX Functions

DAX provides a rich library of functions. Here are a few essential ones:

Aggregation Functions

• SUM(): Adds all the numbers in a column.
• AVERAGE(): Calculates the average of numbers in a column.
• MIN(): Returns the smallest value in a column.
• MAX(): Returns the largest value in a column.
• COUNT(): Counts the number of rows that contain numbers.

Logical Functions

• IF(): Returns one value if a logical condition is TRUE and another value if it is FALSE.
• AND(): Returns TRUE if all arguments are TRUE.
• OR(): Returns TRUE if any argument is TRUE.

Aggregation Example: Total Sales

To calculate the total sales across all transactions, you would create a measure:

Total Sales = SUM(Sales[SalesAmount])

Measures are dynamic calculations that respond to the context of the report or query.

Context in DAX

Understanding context is crucial for writing effective DAX. There are two main types of context:

1. Row Context

Row context refers to the current row being evaluated. This is implicitly present when you create a calculated column, as the formula is evaluated for each row individually.

2. Filter Context

Filter context is the set of filters that have been applied to the data, either by the user interacting with a report or by DAX formulas themselves (e.g., using functions like CALCULATE()).

The CALCULATE() function is one of the most powerful in DAX, as it allows you to modify the filter context under which an expression is evaluated.

Sales in 2023 = CALCULATE( [Total Sales], Dates[Year] = 2023 )

This measure calculates the `Total Sales` but only for the rows where the `Year` in the `Dates` table is 2023.

Calculated Columns vs. Measures

It's important to distinguish between these two:

• Calculated Columns: Are computed row by row and stored in the model. They consume memory and are useful for static calculations or when you need to use the result in row-level operations.
• Measures: Are computed on the fly based on the current filter context. They are not stored in the model and are ideal for aggregations and dynamic calculations that change with user interaction.

When to Use Which:

• Use a calculated column when you need to perform a calculation for each row that will be used in further row-level calculations or grouping.
• Use a measure for aggregations (sum, average, count) that will be displayed in visuals and should respond to slicers and filters.

Next Steps

This tutorial provided a basic introduction to DAX. To further enhance your skills:

• Explore more DAX functions, such as time intelligence functions.
• Practice writing complex formulas to solve real-world business problems.
• Learn about relationships and how they impact DAX calculations.
• Familiarize yourself with the CALCULATE() function and its modifiers.

Continue your learning journey with more advanced tutorials on DAX and Analysis Services.