Core DAX Concepts
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX enables you to define custom calculations and data manipulation beyond what is achievable with standard data modeling techniques. Understanding the core concepts of DAX is crucial for building robust and efficient analytical models.
Formulas and Calculations
DAX formulas are used to create calculated columns, measures, and row-level security roles. They consist of functions, operators, and constants. The syntax is designed to be familiar to users of spreadsheet applications.
Calculated Columns vs. Measures
A fundamental distinction in DAX is between calculated columns and measures:
- Calculated Columns: These are physical columns added to your data model tables. The calculation for a calculated column is performed row by row during data import or refresh and stored in the model. They consume memory and affect model size. Use them when the value needs to be evaluated for each row and potentially used in row context (e.g., grouping by a calculated category).
- Measures: These are dynamic calculations performed at query time. They are not stored in the model but are evaluated based on the current filter context. Measures are ideal for aggregations and calculations that respond to user interactions (slicers, filters) such as sums, averages, and complex KPIs.
Filter Context
Filter context is one of the most important concepts in DAX. It represents the set of filters that are applied to the data model at any given time, influencing the result of a measure. This context can be derived from:
- The report interface (e.g., slicers, visuals, filters).
- Row context within a calculated column.
- Other DAX functions that modify the filter context (e.g.,
CALCULATE).
Understanding how filter context flows and how to manipulate it is key to writing correct DAX.
Row Context
Row context is the current row being processed in a table. This context is inherent in calculated columns, where the formula is evaluated for each row individually. It can also be introduced into measures using iterator functions (like SUMX, AVERAGEX) or by functions like RELATED.
Evaluation Context
Evaluation context is the combination of Filter Context and Row Context. When a DAX formula is evaluated, it operates within the current evaluation context. This context determines which rows are visible and what values are available for calculation.
CALCULATE Function
The CALCULATE function is arguably the most powerful and frequently used function in DAX. It allows you to modify the filter context in which an expression is evaluated. Its syntax is:
CALCULATE(, , , ...) Where <expression> is typically a measure or another DAX expression, and <filter> arguments can be boolean expressions, table filters, or other filter modifications.
Table Functions
DAX includes many functions that return entire tables. These are often used within other DAX functions, particularly CALCULATE, to filter or transform data. Examples include:
FILTER(): Returns a table that has been filtered based on a condition.ALL(): Returns all rows in a table or all values in a column, ignoring any filters that might have been applied.VALUES(): Returns a single-column table of unique values.
Data Types
DAX supports several data types, including integers, decimals, currency, dates, strings, booleans, and more. Understanding data types is important for using functions correctly and ensuring accurate calculations.
Relationships
Relationships between tables in your data model are crucial. DAX functions often traverse these relationships to aggregate data or retrieve related values. Functions like RELATED() and RELATEDTABLE() are used to work with these relationships.
Mastering these core DAX concepts will provide a strong foundation for building sophisticated business intelligence solutions with Analysis Services and Power BI.