What Is DAX? A Practical Guide for Power BI Users

The quick answer

DAX (Data Analysis Expressions) is the formula language used in Power BI, Analysis Services (Tabular), and Power Pivot for Excel. It is used to create calculated columns (new columns computed row by row), measures (aggregated calculations evaluated in the context of a filter), and calculated tables (new tables computed from existing ones). DAX is what transforms a simple dataset into a sophisticated analytical model.

If you are building Power BI reports beyond basic drag-and-drop, you will write DAX. The most important thing to understand about DAX: measures are evaluated in filter context, not row context. This is the fundamental distinction between DAX and Excel formulas, and understanding it is the key to writing DAX correctly.

Row context vs filter context

**Row context** is the context in which calculated columns are evaluated. When a calculated column is computed, DAX iterates through each row of the table and evaluates the formula for that specific row. Row context "knows" which row it is currently on.

**Filter context** is the context in which measures are evaluated. When a measure appears in a visual, DAX evaluates it in the context of all the filters applied to that visual — the current row label, column header, slicer selections, and report-level filters. Filter context does not iterate rows; it aggregates all rows that pass the current filter.

The distinction matters: a measure like SUM(Sales[Revenue]) evaluates the sum of all Revenue values in the current filter context. A calculated column like Sales[Revenue_USD] = Sales[Revenue] * Sales[Exchange_Rate] evaluates the multiplication row by row in row context.

**CALCULATE** is the most important DAX function because it modifies filter context — it allows you to evaluate an expression in a different filter context than the current one. This is how you build measures like "revenue last year," "sales in region X regardless of current filter," or "percentage of total."

Core DAX functions

### Aggregation functions

SUM(Sales[Revenue]) — sum of all values in the Revenue column in the current filter context.

AVERAGE(Sales[Revenue]) — arithmetic mean.

COUNT(Sales[OrderID]) — count of non-blank values.

COUNTROWS(Sales) — count of rows in the table in the current filter context. For counting rows rather than values in a specific column.

DISTINCTCOUNT(Sales[CustomerID]) — count of distinct values. Expensive for large tables in DirectQuery mode; use sparingly.

MIN(Sales[Date]) and MAX(Sales[Date]) — minimum and maximum values.

### CALCULATE

CALCULATE(expression, filter1, filter2, ...) evaluates the expression in a modified filter context where the specified filters are applied.

CALCULATE(SUM(Sales[Revenue]), Sales[Region] = "North") — sum of revenue, but only for North region records, regardless of any other Region filter applied in the visual.

CALCULATE(SUM(Sales[Revenue]), REMOVEFILTERS(Sales[Region])) — sum of revenue with the Region filter removed — the "all regions" total, used to calculate percentage of total.

CALCULATE(SUM(Sales[Revenue]), DATEADD(Calendar[Date], -1, YEAR)) — sum of revenue for the same period in the previous year (time intelligence).

### Time intelligence functions

DAX includes a rich set of time intelligence functions for common period-over-period calculations. These functions require a marked date table (a table with a Date column marked as a date table in the data model).

TOTALYTD(SUM(Sales[Revenue]), Calendar[Date]) — year-to-date revenue, accumulating from the start of the year to the last date in the current filter context.

TOTALQTD, TOTALMTD — quarter-to-date and month-to-date equivalents.

SAMEPERIODLASTYEAR(Calendar[Date]) — the equivalent date period in the previous year. Used inside CALCULATE: CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date])).

DATEADD(Calendar[Date], -1, YEAR) — shift the date context by a specified interval. More flexible than SAMEPERIODLASTYEAR for non-standard period comparisons.

DATESYTD, DATESMTD, DATESQTD — return a table of dates for the specified period, used in CALCULATE to define time windows.

PREVIOUSYEAR, PREVIOUSMONTH, PREVIOUSQUARTER — return date tables for the previous period.

### FILTER and ALL

FILTER(table, condition) — returns a subset of rows from a table matching the condition. Used inside CALCULATE to apply complex filter conditions that cannot be expressed as simple equality conditions.

CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Revenue] > 1000)) — sum of revenue for orders over $1,000 only.

ALL(table or column) — removes all filters from a table or column, returning all rows or all values. Used to calculate totals independent of current filter context.

CALCULATE(SUM(Sales[Revenue]), ALL(Sales)) — total revenue across all rows regardless of current filter — the grand total.

ALLEXCEPT(table, column1, column2) — removes all filters except those on the specified columns. Useful for calculating subtotals that respect one filter dimension but ignore others.

### RELATED and RELATEDTABLE

RELATED(DimCustomer[Segment]) — in the context of a row in a related table (like the Sales fact table), retrieves the value from the related dimension table (DimCustomer). Equivalent to a VLOOKUP. Used in calculated columns, not measures.

RELATEDTABLE(Sales) — in the context of a row in a dimension table (like DimCustomer), returns the related rows from the fact table. Used in calculated columns to count or aggregate related rows.

### SUMX, AVERAGEX, COUNTX (iterator functions)

X-functions iterate over a table row by row, evaluate an expression for each row in row context, and then aggregate the results.

SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — for each row in Sales, multiply Quantity by UnitPrice, then sum the results. This is different from SUM(Sales[Revenue]) if Revenue is not a pre-computed column — it computes revenue at the row level and aggregates.

AVERAGEX(Orders, SUMX(RELATEDTABLE(OrderLines), OrderLines[Amount])) — for each order, calculate the total order amount (summing related order lines), then average those order totals across orders. Complex aggregations that require row-level computation before aggregation use SUMX/AVERAGEX patterns.

### VAR and RETURN

VAR and RETURN allow variable declarations in DAX, which improves readability and performance for complex calculations:

VAR TotalRevenue = SUM(Sales[Revenue])

VAR PYRevenue = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date]))

RETURN DIVIDE(TotalRevenue - PYRevenue, PYRevenue)

Variables are evaluated once and reused, unlike expressions that would be recalculated each time they appear. For DAX expressions that reference the same sub-expression multiple times, using VAR avoids redundant calculation.

### DIVIDE

DIVIDE(numerator, denominator, [alternate_result]) — safe division that returns the alternate_result (default 0) instead of an error when the denominator is 0 or blank. Use DIVIDE for all ratio calculations rather than the division operator (/) which returns an error on zero denominators.

Calculated columns vs measures

**Calculated columns** are computed at data refresh time (or when the model loads) and stored in the model. They add a new column to a table, computed row by row. Use calculated columns for:

- Classifications or segments based on row-level attributes (Customer Segment based on revenue tier)

- Keys for relationships not present in the source

- Pre-computed values used in multiple measures

Calculated columns increase model size (every value is stored). Avoid calculated columns for values that can be computed as measures — prefer a measure that computes the value at query time over a calculated column that stores it in the model.

**Measures** are computed at query time in filter context. They do not store values; they compute them dynamically based on the current filter context. Use measures for:

- Aggregations (sum, average, count, min, max)

- Any calculation that should respond to filter context (slicers, row labels, column headers)

- KPIs and business metrics

The majority of analytical calculations should be measures. Calculated columns are for structural values that do not change with filter context.

Common DAX patterns

**Percentage of total**:

Revenue % of Total = DIVIDE(SUM(Sales[Revenue]), CALCULATE(SUM(Sales[Revenue]), ALL(DimProduct[Category])))

**Running total (YTD)**:

Revenue YTD = TOTALYTD(SUM(Sales[Revenue]), Calendar[Date])

**Period-over-period change**:

VAR CurrentRevenue = SUM(Sales[Revenue])

VAR PriorYearRevenue = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date]))

RETURN DIVIDE(CurrentRevenue - PriorYearRevenue, PriorYearRevenue)

**Customer rank by revenue**:

Customer Rank = RANKX(ALL(DimCustomer), SUM(Sales[Revenue]), , DESC, Dense)

**Moving average (3-month)**:

3M Moving Avg Revenue = AVERAGEX(DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH), [Revenue])

Performance considerations

**Avoid column scanning in measures**: expressions that scan large columns (COUNTROWS of a large fact table with a complex FILTER condition) are expensive. Materialise complex filter conditions as calculated columns or use summary tables.

**Use Import mode for complex DAX**: DirectQuery measures execute as SQL against the source database. Complex DAX with CALCULATE, FILTER, and iterator functions may not translate efficiently to SQL. Import mode computes DAX in memory, where Vertipaq's columnar compression and in-memory computation are fast.

**Avoid high-cardinality calculated columns**: calculated columns with high cardinality (many distinct values) increase model size disproportionately. Prefer measures that compute at query time over calculated columns that enumerate large value sets.

**Use the Vertipaq Analyzer and DAX Studio**: DAX Studio is an external tool (free) for profiling DAX query performance, examining query plans, and identifying slow measures. The Vertipaq Analyzer shows model size, column cardinality, and compression ratios — useful for identifying model size drivers. Both are essential for Power BI performance work.

For the full Power BI performance context including storage mode decisions (Import vs DirectQuery vs Composite), see Power BI performance optimisation. For the comparison between Power BI/DAX and Tableau for enterprise analytics decisions, see Power BI vs Tableau.

Our Power BI consulting practice designs DAX-optimised semantic models and DAX training programmes for enterprise Power BI deployments. If you are building a complex Power BI model or troubleshooting slow DAX measures, book a free 30-minute audit.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.