Microsoft Docs

DAX Overview

What is DAX?

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, in Power Pivot in Excel, SQL Server Analysis Services (SSAS) tabular models, and Power BI. DAX enables you to create custom calculations for tabular data models using existing data, including calculations, aggregations, and custom data-shaping.

DAX is designed for business intelligence and data modeling, allowing you to:

• Create calculated columns and measures.
• Perform complex analytical calculations.
• Define sophisticated business logic.
• Enhance the usability and insight of your data models.

Key Concepts

Understanding the following key concepts is crucial for working effectively with DAX:

Formulas

DAX formulas are used to define calculations. They begin with an equals sign (=) and consist of functions, operators, and values.

Tables and Columns

DAX operates on tables and columns within a tabular data model. Formulas often reference tables and columns by their names.

Evaluation Context

This is perhaps the most important concept in DAX. The evaluation context determines how a DAX formula is evaluated, and it can be modified by functions such as CALCULATE, row context, and filter context.

• Row Context: The current row being processed in a table.
• Filter Context: The set of filters applied to the data, which can originate from slicers, filters in visuals, or other DAX expressions.

Measures vs. Calculated Columns

DAX formulas can create two primary types of calculations:

• Measures: Calculations that are computed dynamically based on the filter context. They are typically used for aggregations like sums, averages, and ratios. Measures are not stored in the table; they are calculated on the fly.
• Calculated Columns: Calculations that are computed row by row within a table and stored as part of the data model. They are useful for deriving new attributes from existing data.

Basic DAX Syntax

A typical DAX formula structure looks like this:

FunctionName(Argument1, Argument2, ...)

Common Functions

DAX provides a rich library of functions for various purposes. Some of the most fundamental include:

• Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT
• Logical Functions: IF, AND, OR, SWITCH
• Date and Time Functions: TODAY, NOW, YEAR, MONTH, DAY
• Text Functions: CONCATENATE, LEFT, RIGHT, LEN
• Relationship Functions: RELATED, RELATEDTABLE
• Filter and Context Modification Functions: CALCULATE, FILTER, ALL, ALLEXCEPT

Example: Calculating Total Sales

Here's a simple example of a DAX measure to calculate the total sales from a table named Sales with a column named Amount:

Total Sales = SUM(Sales[Amount])

In this example:

• Total Sales is the name of the measure.
• = indicates the start of a formula.
• SUM is an aggregation function.
• Sales[Amount] refers to the Amount column in the Sales table.

Example: Calculating Sales Year-to-Date (YTD)

A more complex example, calculating YTD sales, showcases the power of DAX's time intelligence functions:

Sales YTD =
TOTALYTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

This requires a dedicated Date table marked as a date table in your model.

Resources

For further learning and in-depth information, explore these resources:

• Microsoft Learn - DAX Documentation
• Power BI DAX Guide
• SQL Server Analysis Services Overview

DAX is a powerful language that, once mastered, can unlock deep insights from your data. Practice and exploration are key to becoming proficient.