The DAX concepts that actually matter in production Power BI models — the difference between row context and filter context, CALCULATE and its many uses, time intelligence patterns, iterating functions, and the performance implications of common DAX patterns.
DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services, and Power Pivot. It is also one of the most commonly misunderstood tools in the Microsoft analytics stack. The reason is that DAX evaluation — particularly the interaction between row context and filter context — is counterintuitive compared to SQL or Excel formula logic. Once the context model clicks, DAX becomes powerful. Until it does, it produces confusing results and hard-to-debug errors.
This guide focuses on the concepts that actually matter in production Power BI models: context evaluation, CALCULATE, time intelligence, iterating functions, and the performance implications of common patterns.
Row Context vs Filter Context
The single most important concept in DAX. Almost every confusing DAX result can be traced to a context misunderstanding.
**Row context** is created by calculated columns and iterating functions (SUMX, AVERAGEX, etc.). It represents the current row being evaluated. In a calculated column, every row has its own row context — expressions are evaluated for each row individually.
In the Sales table, a calculated column:
Gross Margin = Sales[Revenue] - Sales[Cost]
This works because Sales[Revenue] and Sales[Cost] refer to values in the current row — DAX evaluates this for row 1, then row 2, etc. The row context is implicit.
**Filter context** is the set of filters active when a measure is evaluated. In a report, filter context is created by slicers, row/column groupings in a matrix, visual-level filters, and page/report filters. A measure that calculates Total Revenue does not have a row context — it aggregates all rows that match the current filter context.
Total Revenue = SUM(Sales[Revenue])
When this measure appears in a matrix grouped by Product Category, the filter context for each cell includes the Product Category filter — SUM operates on only the rows where Product Category matches.
**The confusion:** Row context does not automatically become filter context. If you use SUM inside a SUMX (an iterating function that creates row context), the SUM still operates on the full dataset — it does not filter to the current row. This is where RELATED and CALCULATE come in.
**CALCULATE** transitions context. It evaluates an expression inside a modified filter context. It also performs context transition — converting the current row context into an equivalent filter context, allowing aggregation functions to "see" the current row's values as filters.
CALCULATE: The Most Important DAX Function
CALCULATE does two things: it evaluates an expression in a modified filter context, and (when called inside a row context) it performs context transition.
Basic usage — evaluating Total Revenue for only completed orders:
Completed Revenue = CALCULATE(SUM(Sales[Revenue]), Sales[Status] = "Completed")
CALCULATE replaces the existing filter on Sales[Status] with the new filter. Other filters from the report filter context (date slicer, region filter) remain active.
**Adding filters:** Multiple filter arguments in CALCULATE are combined with AND logic:
Revenue High Value Completed =
CALCULATE(
SUM(Sales[Revenue]),
Sales[Status] = "Completed",
Sales[OrderValue] > 10000
)
**Removing filters with ALL:** ALL removes filters from a specific column or table. This is how you build "total" and "percentage of total" measures:
Total Revenue All Regions =
CALCULATE(SUM(Sales[Revenue]), ALL(DimGeography[Region]))
Revenue % of Total =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(SUM(Sales[Revenue]), ALL(DimGeography[Region]))
)
**ALLEXCEPT:** Removes all filters from a table except the specified columns. Useful for "percentage of category" — keep the product category filter but remove all other filters:
Revenue % of Category =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(DimProduct, DimProduct[Category]))
)
**KEEPFILTERS:** Adds a filter without replacing the existing filter on that column — the new filter is intersected with the existing one. Useful when you want to add a constraint without overriding report-level filters.
Time Intelligence Functions
Time intelligence functions require a properly configured date table — a single contiguous date table (no gaps, no duplicates) marked as a date table in the Power BI model, with a relationship to every fact table's date column.
**SAMEPERIODLASTYEAR:** Returns the same period in the previous year. Combined with a base measure:
Revenue LY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
Revenue YoY % =
DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY])
**DATEADD:** Generalised date shifting. DATEADD(DimDate[Date], -1, YEAR) is equivalent to SAMEPERIODLASTYEAR. More flexible for non-standard periods (shifting by quarters, months, custom intervals):
Revenue Prior Quarter =
CALCULATE([Total Revenue], DATEADD(DimDate[Date], -1, QUARTER))
**DATESYTD:** Returns a date table from the start of the year to the current date, enabling year-to-date aggregations:
Revenue YTD = CALCULATE([Total Revenue], DATESYTD(DimDate[Date]))
Revenue YTD LY =
CALCULATE([Total Revenue], DATESYTD(SAMEPERIODLASTYEAR(DimDate[Date])))
**DATESINPERIOD:** Returns a date range relative to a reference date — useful for rolling N-day or N-month windows:
Revenue Rolling 90d =
CALCULATE(
[Total Revenue],
DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -90, DAY)
)
Iterating Functions (X Functions)
SUMX, AVERAGEX, MAXX, MINX, COUNTX, and RANKX all iterate over a table row by row, evaluating an expression per row and aggregating the results.
SUMX is distinct from SUM:
- SUM(Sales[Revenue]) aggregates the Revenue column directly
- SUMX(Sales, Sales[Revenue]) iterates the Sales table, returns Revenue for each row, then sums — equivalent for simple columns but more powerful for expressions
Where SUMX adds power:
Gross Profit = SUMX(Sales, Sales[Revenue] - Sales[Cost])
You cannot write SUM(Sales[Revenue] - Sales[Cost]) — SUM only takes a single column reference. SUMX evaluates the expression per row, then aggregates.
**RANKX:** Ranks values in a column. Common use case — ranking products by revenue within the current filter context:
Product Revenue Rank =
RANKX(
ALL(DimProduct[ProductName]),
[Total Revenue],
,
DESC,
Dense
)
RANKX requires ALL() to rank across all products rather than just the ones visible in the current filter context. Dense ranking (no gaps for ties) is usually appropriate for business rankings.
**TOPN with SUMX:** Return the top N rows and aggregate them:
Top 5 Product Revenue =
SUMX(
TOPN(5, VALUES(DimProduct[ProductName]), [Total Revenue]),
[Total Revenue]
)
Performance Implications
**Avoid large calculated columns.** Calculated columns are computed at model refresh time and stored in memory. A calculated column on a 100-million-row table that performs complex string operations significantly increases memory usage and refresh time. Prefer measures over calculated columns for logic that is used in visual calculations.
**Avoid iterating over large tables in measures.** SUMX(SalesHistory, [Complex Measure]) iterates every row in SalesHistory for every cell in the visual. A visual with 100 cells and a SalesHistory table with 10 million rows executes the expression 1 billion times. This is the most common DAX performance problem.
**Use variables for reused sub-expressions.** DAX evaluates each expression independently. If the same sub-expression appears multiple times, it is evaluated multiple times. Variables (VAR ... RETURN) cache the result:
Revenue YoY % =
VAR CurrentRevenue = [Total Revenue]
VAR PriorRevenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN DIVIDE(CurrentRevenue - PriorRevenue, PriorRevenue)
Without VAR, [Total Revenue] would be evaluated twice. With VAR, it is evaluated once and reused.
**DIVIDE over division.** Always use DIVIDE(numerator, denominator) instead of numerator/denominator for ratios. DIVIDE returns BLANK() instead of an error when the denominator is zero, avoiding error propagation through reports.
**Measure dependencies.** Measures that reference other measures create dependency chains. Deep dependency chains (a measure referencing a measure referencing a measure, five levels deep) can be slow because each level is recalculated. Flatten dependency chains where performance is critical by inlining the sub-expression.
DAX mastery comes from understanding filter context thoroughly and applying it to the CALCULATE function variants. Almost every DAX pattern — time intelligence, percentage of total, period comparisons, rolling windows, rankings — is a variation on CALCULATE with context manipulation. Once that mental model is solid, the rest follows.
Our data architecture consulting and BI consulting practice works with Power BI environments as well as Tableau — contact us if you need support with Power BI data modelling or DAX development.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →