DAX & Advanced Calculations

DAX (Data Analysis Expressions) is the formula language behind Power BI, SSAS Tabular and Power Pivot. In the previous topic you learned DAX basics — SUM, AVERAGE, CALCULATE with simple filters. This topic takes you to the next level: time intelligence (year-over-year, YTD, rolling averages), advanced context manipulation (FILTER, ALL, ALLEXCEPT, VALUES), variables for readability and performance, and the patterns that experienced BI developers use every day.

DAX is not just a function library — it is a context-based language. Understanding filter context and row context is the key that unlocks every advanced pattern. By the end of this topic, you will be able to write any DAX expression a BI project requires and optimize it for performance.

Continue from Power BI Deep Dive where you learned basic DAX (SUM, CALCULATE, DIVIDE) and data modeling. This topic builds directly on those foundations.

Every DAX expression evaluates inside a context — the invisible set of filters that determines which rows a formula sees. There are two types:

Filter context is the set of active filters on the data model when a measure evaluates. When a visual shows "Revenue for Electronics in Q1," the filter context includes dim_product[category] = "Electronics" and dim_date[quarter] = "Q1". CALCULATE can modify this context.

Row context is the current row when a formula evaluates row-by-row (in a calculated column or inside an iterator like FILTER/SUMX). It is like a pointer moving through each row.

The critical insight: measures operate in filter context; calculated columns operate in row context; iterators (SUMX, FILTER, etc.) create their own row context inside a filter context.

CALCULATE(expression, filter1, filter2, ...) evaluates an expression while modifying the filter context. Each filter argument can be:

• A boolean expression — dim_product[category] = "Electronics" — adds a filter to the context
• A table expression — FILTER(ALL(dim_date), dim_date[year] = 2025) — replaces the filter on that table
• A direct column filter — dim_date[year] = 2025 — auto-wrapped in FILTER(ALL(...), ...)

Key behaviors:

• CALCULATE removes existing filters on columns it filters, then applies the new filter
• Filters in CALCULATE are applied after the expression evaluates its inner context
• Nested CALCULATEs propagate context outward — each CALCULATE can modify what the next sees

These functions remove filters from the context — they are the "undo" buttons of DAX:

Variables make DAX readable, debuggable and often faster. A variable stores a result once and reuses it — avoiding repeated calculation.

• VAR defines a named intermediate result (a table or scalar value)
• RETURN is required as the last statement — it outputs the final result
• Variables are evaluated in the context where they are defined — they do NOT inherit the filter context of a subsequent CALCULATE
• Use variables whenever a calculation has 2+ steps or a value is used more than once

Time intelligence is the most common advanced DAX pattern. These functions rely on a properly marked date table (a contiguous date column marked as a date table in the model).

Common advanced DAX patterns used in production BI:

Cumulative / Running Total:

1. Use variables — store intermediate results to avoid recalculating the same expression
2. Avoid FILTER on large tables — CALCULATE(SUM(revenue), dim_product[category] = "X") is faster than CALCULATE(SUM(revenue), FILTER(dim_product, dim_product[category] = "X"))
3. Prefer columns over tables in filter arguments — filter specific columns, not entire tables
4. Use SUMX/ADDCOLUMNS sparingly — iterators are row-by-row and slower than aggregate functions on large tables
5. Minimize DISTINCTCOUNT — it is expensive; use approximate functions or pre-compute if possible
6. Reduce model size — remove calculated columns, use appropriate data types, disable auto date/time if not needed
7. Use DAX Studio to analyze query plans and identify bottlenecks

Use Power BI Desktop with the AdventureWorksDW sample database or any sales dataset with a proper date dimension.

1. Write a Revenue YoY Growth measure using SAMEPERIODLASTYEAR and variables.
2. Write a YTD Revenue measure using TOTALYTD.
3. Write a Rolling 3-Month Average Revenue measure using DATESINPERIOD.
4. Write a Percent of Total Revenue by Category measure using ALL.
5. Write a Cumulative Revenue measure using FILTER and ALL(dim_date).
6. Write a Customer Segment measure using SWITCH to classify customers by lifetime revenue.
7. Use DAX Studio to run each measure and check the query plan — identify any full table scans.
8. Move to Tabular Editor and organize your measures into display folders.

DAX mastery separates a Power BI user from a Power BI developer. In production, you will write time intelligence for fiscal year reports, dynamic segmentation for customer analytics, cumulative charts for goal tracking and complex KPIs that combine multiple business rules. The patterns in this topic — CALCULATE, ALL, variables, time intelligence — are the vocabulary of every advanced Power BI model. SQLBI's DAX patterns repository is the reference you will return to throughout your career.

Business problem: A subscription SaaS company needs advanced analytics: year-over-year revenue growth, rolling averages, customer segmentation, cohort analysis and cumulative revenue tracking.

Dataset: use a subscription dataset with fact_revenue (date, customer_key, mrr), dim_customer (signup_date, plan, segment) and dim_date (full date dimension marked as date table).

Objective: write 10 advanced DAX measures that demonstrate mastery of the patterns from this topic.