Microsoft Learn

Introduction to DAX

Data Analysis Expressions (DAX) is a formula expression language used in Power BI, Analysis Services, and Power Pivot in Excel. It's designed for working with data model data, enabling you to create custom calculations and retrieve data from your models.

DAX formulas are similar to spreadsheet formulas, but they operate on tables and columns within your data model. Mastering DAX is crucial for unlocking the full potential of these tools and building powerful analytical solutions.

Core Concepts

Evaluation Context

The evaluation context is one of the most fundamental concepts in DAX. It determines how a DAX expression is evaluated. There are two primary types of evaluation contexts:

Filter Context

The filter context defines the set of filters applied to the data model before a DAX expression is evaluated. This context is created by slicers, filter panes, row and column headers in PivotTables, and other filters applied within DAX formulas themselves. It's how you specify "what data do I want to see?".

Row Context

The row context refers to the current row being processed in a table. This is common when iterating over tables, such as in calculated columns or when using iterator functions. It allows you to perform calculations based on the values in the current row.

DAX Functions

DAX provides a rich library of functions categorized by their purpose. Here are some common categories:

Aggregation Functions

These functions perform calculations across a set of values, such as summing, averaging, or counting. Examples include:

• SUM(Table[Column]): Returns the sum of all numbers in a column.
• AVERAGE(Table[Column]): Returns the average of all numbers in a column.
• COUNT(Table[Column]): Counts the number of rows that contain values.
• DISTINCTCOUNT(Table[Column]): Counts the number of distinct values in a column.

Time Intelligence Functions

These functions are specifically designed for performing calculations on data that spans across dates and times. They are essential for financial reporting and trend analysis. Examples include:

• DATESYTD(DatesColumn): Returns a table of dates for the year to date.
• TOTALYTD(Expression, DatesColumn): Returns the cumulative total of an expression for the year to date.
• SAMEPERIODLASTYEAR(DatesColumn): Returns a table of dates shifted back one year.

Logical Functions

These functions perform conditional tests and return different results based on those tests. Examples include:

• IF(Logical_Test, Value_If_True, Value_If_False): Returns one value or another depending on whether the logical test is true or false.
• SWITCH(Expression, Value1, Result1, Value2, Result2, ..., Else): Evaluates an expression against a list of values and returns a result corresponding to the first match.

Text Functions

These functions manipulate text strings. Examples include:

• CONCATENATE(String1, String2): Joins two text strings.
• LEFT(Text, Num_Chars): Returns the specified number of characters from the start of a text string.
• FIND(Find_Text, Within_Text, [Start_Num]): Returns the starting position of one text string within another.

Iterator Functions

These functions iterate over each row of a table and perform an expression for each row. They are powerful for complex calculations that require row-by-row logic. Examples include:

• SUMX(Table, Expression): Returns the sum of an expression evaluated for each row in a table.
• AVERAGEX(Table, Expression): Returns the average of an expression evaluated for each row in a table.
• FILTER(Table, FilterExpression): Returns a table that has been filtered down to a subset of rows and columns.

-- Example of SUMX to calculate total sales amount per product
TotalSalesPerProduct =
SUMX(
    Products,
    Products[Quantity] * Products[Price]
)

Creating Measures

Measures are dynamic calculations that respond to the context in which they are used. They are typically used in visualizations to aggregate data. Measures are created using DAX formulas and do not consume memory for each row in the underlying table.

-- Example Measure: Total Sales Amount
Total Sales = SUM(Sales[SalesAmount])

Calculated Columns

Calculated columns are new columns added to a table, where the values are computed based on a DAX formula row by row during data refresh. They consume memory and are stored within the data model.

-- Example Calculated Column: Full Name
FullName = Customers[FirstName] & " " & Customers[LastName]

Best Practices

• Use Measures for Aggregations: Prefer measures over calculated columns for aggregations to improve performance and flexibility.
• Write Readable DAX: Use clear naming conventions, indentation, and comments (though not shown in this simulated response) to make your formulas understandable.
• Understand Evaluation Context: This is paramount for writing correct DAX.
• Leverage `CALCULATE` wisely: It's one of the most powerful functions but can be complex.
• Optimize for Performance: Avoid complex row-by-row calculations where table-level operations suffice.
• Use Relationships: Ensure your data model relationships are correctly defined, as DAX heavily relies on them.

Performance Tuning

Efficient DAX is crucial for responsive reports and analyses. Key considerations include:

• Minimizing the number of evaluated rows.
• Simplifying filter contexts.
• Using appropriate DAX functions.
• Optimizing data model design (e.g., star schema).
• Understanding query folding when working with DirectQuery.