36  DAX (Data Analysis Expressions) Fundamentals

36.1 Why DAX Matters

If Power Query is where the data is prepared, DAX is where the analysis is expressed.

DAX (Data Analysis Expressions) is the formula language of the Microsoft tabular-model family — Power BI, Power Pivot in Excel, Analysis Services Tabular. Where Power Query reshapes the data at refresh time (Chapter 35), DAX runs at query time, when the user filters or interacts with the dashboard. Every measure on a Power BI report — every YoY growth percentage, every YTD total, every variance-to-target indicator — is a DAX expression evaluated against the user’s current context.

The standard reference is The Definitive Guide to DAX by Marco Russo & Alberto Ferrari (2019), the most thorough treatment of the language. Introducing Microsoft Power BI by Alberto Ferrari & Marco Russo (2016) sets DAX in the broader Power BI context for analysts who are new to the platform.

For a visualisation-focused book, this chapter is where Power BI’s analytical intelligence lives. A dashboard’s correctness, performance, and flexibility all rest on its DAX measures.

36.2 DAX in Context: Excel, Power Query, and the Tabular Model

TipDAX versus Adjacent Languages
Language When It Runs What It Operates On Returns
Excel formulas When the workbook recalculates Cells in a worksheet Cell values
Power Query M At refresh time Tables (full datasets) A reshaped table
DAX At query time (every interaction) Tabular model (tables + relationships) Scalar values or tables in response to user filters
SQL When executed on the source Database tables Result sets

The conceptual shift for an Excel-trained analyst: DAX does not operate on cells. It operates on columns and tables, with the answer adapting to whatever filters are active in the visual. The same measure renders different numbers in every cell of a matrix because each cell carries a different filter context.

36.3 Calculated Columns versus Measures

DAX produces two kinds of result, distinguished by when they compute:

TipColumns versus Measures
Property Calculated Column Measure
When computed Once at refresh, stored in the model Every time the user interacts with a visual
Memory Materialised; takes storage Computed on the fly; no storage
Operates on Each row independently Aggregations across rows
Use as A field on rows, columns, slicers, filters A field on values
Example Profit Margin = [Profit] / [Sales] (per row) Profit Margin = DIVIDE(SUM([Profit]), SUM([Sales]))
Best for Categorisation, banding, lookups Aggregated KPIs, ratios, time intelligence

A common rule: prefer measures over calculated columns unless the result genuinely needs to be a row attribute (a band label, a category lookup). Measures keep the model lean and respond to filter context dynamically.

36.4 The Two Evaluation Contexts

Every DAX expression evaluates in a context — the set of filters and rows that determine which data the expression sees. There are exactly two:

  • Filter Context: The set of filters applied by the visual, slicers, page filters, and any explicit CALCULATE modifications. Determines which rows the expression considers.
  • Row Context: The current row being iterated when DAX is processing a column expression or an iterator function (X-suffix functions like SUMX).

Understanding when each context is active is the most consequential idea in DAX. The most-asked DAX question — why does my measure return the same number on every row? — is almost always about a missing or misunderstood context transition.

36.5 CALCULATE: The Central Function

CALCULATE is the most important function in DAX. It evaluates an expression in a modified filter context.

CALCULATE(
    expression,
    filter1,
    filter2,
    ...
)

The filter arguments add to, override, or remove filters from the existing filter context. Examples:

// Sales last year
Sales LY =
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Sales for Mumbai only, regardless of region slicer
Mumbai Sales =
CALCULATE(
    SUM(Sales[Amount]),
    'Customer'[City] = "Mumbai"
)

// Sales ignoring all filters
Total Sales =
CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS()
)

CALCULATE also performs context transition: when invoked inside a row context (a calculated column or an iterator), it converts the current row into a filter that propagates to the rest of the model — the mechanism behind virtually every Power BI measure of any sophistication.

36.6 Common Function Families

TipDAX Functions an Analyst Uses Daily
Family Functions Use
Aggregation SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS, DISTINCTCOUNT Summarise values
Iteration (X) SUMX, AVERAGEX, COUNTX, MAXX, MINX Iterate row-by-row in a row context
Filter CALCULATE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS Modify filter context
Time Intelligence DATEADD, DATESYTD, SAMEPERIODLASTYEAR, TOTALYTD, DATESINPERIOD, PARALLELPERIOD, PREVIOUSYEAR Period-over-period analysis
Logical IF, SWITCH, AND, OR, NOT, IFERROR Branching and error handling
Text CONCATENATE, & operator, FORMAT, LEFT, RIGHT, MID, LEN, UPPER, LOWER Text manipulation
Date TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, EDATE, EOMONTH Date arithmetic
Relationship RELATED, RELATEDTABLE, USERELATIONSHIP Traverse model relationships
Table-Returning VALUES, DISTINCT, SUMMARIZE, ADDCOLUMNS, SELECTCOLUMNS Return tables for use in CALCULATE or visuals
Ranking and TopN TOPN, RANKX Top-N selection and ranking

A few worked examples:

// Iterator: profit margin computed per row, then averaged
Profit Margin =
DIVIDE(
    SUM(Sales[Profit]),
    SUM(Sales[Amount])
)

Avg Margin per Order =
AVERAGEX(
    Sales,
    DIVIDE(Sales[Profit], Sales[Amount])
)

// Filter: top 10 products
Top 10 Product Sales =
CALCULATE(
    SUM(Sales[Amount]),
    TOPN(10, VALUES('Product'[Name]), [Total Sales])
)

// Conditional: high / medium / low band
Order Band =
SWITCH(
    TRUE(),
    [Total Sales] >= 5000, "High",
    [Total Sales] >= 1000, "Medium",
    "Low"
)

36.7 Variables in DAX

Variables (VAR ... RETURN) make DAX expressions readable, performant, and debuggable.

YoY Growth % =
VAR CurrentSales = SUM(Sales[Amount])
VAR LastYearSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
VAR Growth = DIVIDE(CurrentSales - LastYearSales, LastYearSales)
RETURN
    Growth

Three rules of thumb for variables:

  • Compute once, reference many: A variable is evaluated once; reusing it across the expression saves repeat computation.
  • Variables are evaluated in the filter context where they are declared, not where they are used. This immutability is part of why variables make DAX easier to reason about.
  • Use variables for clarity: Even if a measure does not need them for performance, variables document the intermediate steps and make the formula readable.

Marco Russo & Alberto Ferrari (2019) emphasise that any DAX measure beyond two or three lines should be refactored using variables.

36.8 Time Intelligence

Time-intelligence DAX is what makes Power BI’s period-over-period reporting feel effortless. Prerequisites:

  • A date table with one row per calendar date covering the full range of the data.
  • The date table marked as a Date Table (Modeling → Mark as Date Table → choose the date column).
  • A single relationship from the fact table’s date column to the date table.

Common patterns:

// Year-to-date sales
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

// Same period last year
Sales SPLY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

// Year-over-year growth
YoY Growth = DIVIDE([Total Sales] - [Sales SPLY], [Sales SPLY])

// Trailing twelve months
Sales TTM =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

// Previous month
Sales PM = CALCULATE([Total Sales], PARALLELPERIOD('Date'[Date], -1, MONTH))

Without the date-table setup, time-intelligence DAX returns blank silently — one of the more frustrating troubleshooting scenarios in Power BI.

36.9 Common Patterns

A handful of recurring DAX patterns cover most of the day-to-day analytical work:

  • Variance to Target: Variance = [Total Sales] - [Target Sales]
  • Variance Percent: Variance % = DIVIDE([Variance], [Target Sales])
  • Running Total: Running Total = CALCULATE([Total Sales], DATESYTD('Date'[Date]))
  • Percent of Total: Percent of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product')))
  • Distinct Customer Count: Customer Count = DISTINCTCOUNT(Sales[CustomerID])
  • Average Order Value: AOV = DIVIDE([Total Sales], [Order Count])
  • Days Since Last Purchase: Recency = DATEDIFF(MAX(Sales[Date]), TODAY(), DAY)
  • Top 5 Products by Sales: Top 5 Sales = CALCULATE([Total Sales], TOPN(5, VALUES('Product'[Name]), [Total Sales]))

A useful institutional discipline is to maintain a measure library — a single Power BI table containing every reusable measure with consistent naming and documentation.

36.10 Performance and Best Practices

DAX performance rests on a small set of habits:

  • Prefer measures over calculated columns: Columns inflate the model; measures compute on demand.
  • Use variables: Avoid repeat evaluation of the same sub-expression.
  • Minimise context transitions: Each one is computationally expensive on large models.
  • Keep date table relationships single-direction: Bidirectional filtering is rarely needed and almost always slows the model.
  • Avoid implicit measures: A field dragged onto Values uses an implicit measure (e.g., Sum of Amount); explicit measures are clearer and faster.
  • Use star-schema models (covered in Chapter 25): DAX is engineered for star schemas; flat or snowflake models perform worse.
  • Profile with Performance Analyzer: View → Performance Analyzer → Start Recording → interact with visuals → see DAX query times per visual.
  • Use DAX Studio externally for deep query tuning, server timings, and execution plans.

36.11 Common Pitfalls

  • Calculated Column Where a Measure Should Be: A profit-margin calculated column duplicates per row when a measure would suffice; model bloats and aggregation is wrong.
  • Implicit Measure Surprise: Field dragged to Values aggregates as Sum by default; for some columns (year, ID, percentage) the sum is meaningless.
  • Row Context Forgotten: A measure inside a calculated column expects row context but encounters filter context (or vice versa); silent context transition issues.
  • No Date Table: Time-intelligence DAX returns blank because the date table is missing, unmarked, or not related to the fact table.
  • Bidirectional Relationships: Bidirectional filters create ambiguity and performance problems; almost never the right choice for a star schema.
  • CALCULATE With Many Filters Not Wrapped in FILTER: Each direct filter argument acts as a Boolean test; complex filter logic should be wrapped in FILTER() for clarity.
  • DIVIDE Forgotten: Plain / returns infinity or error on division by zero; DIVIDE(numerator, denominator, 0) is safer.
  • Variables Mutated: A variable evaluated in one context cannot be re-evaluated in another; mistaking a variable for a re-evaluable expression produces unexpected blanks.
  • No Documentation: Twenty measures with cryptic names; no analyst can maintain them later.

36.12 Illustrative Cases

A YoY Dashboard Built on Three DAX Measures

A retail performance dashboard answers am I growing? using just three measures: Total Sales, Sales SPLY, and YoY Growth %. With a properly marked date table and a star-schema model, the same three measures drive a line chart, a KPI card, and a year-month matrix without further code.

A Customer 360 Built on a Measure Library

A bank standardises its customer-analytics workbooks around a common measure library — Customer Count, Active Customer Count, New Customer Count, Churned Count, Average Tenure, Average Balance, Lifetime Value, Recency Days. Every workbook references the same library; reports across functions agree on every number.

A Performance Save by Removing Bidirectional Filtering

A 50-million-row Power BI model is slow on every visual. Inspection reveals five bidirectional relationships left over from a casual setup. Switching all to single-direction (with USERELATIONSHIP for the rare cases needing bidirectional behaviour) cuts visual render times from 4 seconds to 200 milliseconds.

36.13 Hands-On Exercise: Building a Power BI Measure Library

Aim: Build a working DAX measure library for Yuvijen Stores covering ten common analytical measures, organised in a dedicated Measures table, with variables and time-intelligence patterns.

Deliverable: A Power BI file (.pbix) with a star-schema model, a properly marked date table, and ten DAX measures organised in a Measures table with documentation.

36.13.1 Step 1 — Build the Star Schema

Use the sales.csv and dimension tables introduced in earlier chapters (or the model from Chapter 25):

  • Sales (fact table) related to dim_customer, dim_product, dim_store, and dim_date.
  • Mark dim_date as a Date Table (Modeling → Mark as Date Table).

Verify the star schema in the Model view: one fact in the centre, four dimensions outward.

36.13.2 Step 2 — Create a Measures Table

A common Power BI pattern is to keep all measures in a dedicated empty table:

  1. Home → Enter Data.
  2. Name the table Measures. Add a single dummy column. Click Load.
  3. After creating the first measure (next step), the dummy column can be hidden (right-click → Hide).
  4. Right-click any column in the Measures table → Mark as Measure Table (or simply drag measures into it).

This keeps the model’s organisational pane tidy and the measures easy to find.

36.13.3 Step 3 — Build the Foundation Measures

Total Sales = SUM(Sales[Amount])

Total Profit = SUM(Sales[Profit])

Profit Margin =
DIVIDE([Total Profit], [Total Sales])

Order Count = COUNTROWS(Sales)

Customer Count = DISTINCTCOUNT(Sales[CustomerID])

Avg Order Value = DIVIDE([Total Sales], [Order Count])

Format each measure appropriately (currency, percentage, integer) by selecting the measure in the Fields pane and using the Format dropdown.

36.13.4 Step 4 — Add Time-Intelligence Measures

Sales YTD = TOTALYTD([Total Sales], 'dim_date'[Date])

Sales SPLY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('dim_date'[Date]))

YoY Growth =
VAR Current = [Total Sales]
VAR Prior = [Sales SPLY]
RETURN DIVIDE(Current - Prior, Prior)

Sales TTM =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('dim_date'[Date], MAX('dim_date'[Date]), -12, MONTH)
)

These four together let any visual show YTD, prior-year comparison, growth, and trailing-twelve-month figures without further code.

36.13.5 Step 5 — Add a Variance-to-Target Measure

Assume a Targets table exists with monthly target sales per region:

Target Sales = SUM(Targets[TargetAmount])

Variance to Target =
VAR Actual = [Total Sales]
VAR Target = [Target Sales]
RETURN DIVIDE(Actual - Target, Target)

The measure works on any visual that filters by region or month — the dashboard does not need a separate query.

36.13.6 Step 6 — Document the Measures

For each measure, click in the Fields pane and use the Properties panel:

  • Description: A short note explaining the measure (visible in tooltips for users browsing the field list).
  • Display Folder: Group related measures (e.g., Time Intelligence, Customer KPIs).
  • Format: Apply the right format (Currency ₹, Percentage, Whole Number).

The result is a self-documenting field list that other analysts can use without re-deriving each measure.

36.13.7 Step 7 — Build a Demonstration Dashboard

Create a one-page Power BI report that exercises every measure:

  • KPI cards: Total Sales, Profit Margin, Customer Count, Avg Order Value.
  • Line chart: Total Sales and Sales SPLY overlaid by month.
  • Bar chart: YoY Growth by region.
  • Card: Variance to Target.
  • Matrix: Total Sales, Sales YTD, Sales TTM by month.

Every visual draws on the measure library; no measure is rebuilt in any visual.

36.13.8 Step 8 — Performance Check

  1. View → Performance Analyzer → Start Recording.
  2. Refresh the report. Tableau records DAX query times per visual.
  3. Stop the recording.
  4. Identify any visual taking more than 500 ms.
  5. For slow ones, examine the DAX in DAX Studio (free external tool) to inspect storage-engine and formula-engine timings.

A measure library built with the principles in this chapter typically renders every visual in under 200 ms even on million-row datasets.

36.13.9 Step 9 — Connect to the Visualisation Layer

The hands-on demonstrates the central truth of DAX-driven dashboards:

  • A dashboard’s analytical capability is encoded in its measures. The visuals are containers; the measures are the meaning.
  • Time intelligence that would take twenty minutes to express in SQL takes one DAX line.
  • A measure library is reusable institutional knowledge: once built, it serves every workbook the firm publishes on this data model.

The same star-schema-plus-measures pattern is the foundation of every Power BI dashboard at any scale.

TipFiles and Screen Recordings

Power BI file (yuvijen-dax-library.pbix), the source data, and screen recordings of each measure being built and exercised will be embedded here.

Summary

Concept Description
Foundations
Why DAX Matters If Power Query is where data is prepared, DAX is where the analysis is expressed
DAX Definition Formula language of the Microsoft tabular-model family
DAX vs Excel Formulas Excel formulas operate on cells; DAX operates on columns and tables
DAX vs Power Query M M runs at refresh time on tables; DAX runs at query time on the tabular model
DAX vs SQL SQL runs on the source; DAX runs on the in-memory model with relationships
Calculated Columns versus Measures
Calculated Column Computed once at refresh and stored; per-row attribute
Measure Computed every interaction; aggregations across rows
Prefer Measures Rule Prefer measures over columns unless the result genuinely needs to be a row attribute
Evaluation Contexts
Filter Context Set of filters applied by visuals, slicers, and CALCULATE
Row Context Current row being iterated when DAX processes a column expression
CALCULATE Most important DAX function; evaluates expression in modified filter context
Context Transition CALCULATE inside a row context turns the current row into a filter for the rest of the model
Function Families
Aggregation Functions SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS, DISTINCTCOUNT
Iterator Functions SUMX, AVERAGEX, COUNTX, MAXX, MINX iterate row-by-row
Filter Functions CALCULATE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS
Time Intelligence Functions DATEADD, DATESYTD, SAMEPERIODLASTYEAR, TOTALYTD, DATESINPERIOD, PARALLELPERIOD
Logical Functions IF, SWITCH, AND, OR, NOT, IFERROR
Text Functions CONCATENATE, FORMAT, LEFT, RIGHT, MID, LEN, UPPER, LOWER
Date Functions TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, EDATE, EOMONTH
Relationship Functions RELATED, RELATEDTABLE, USERELATIONSHIP
Table-Returning Functions VALUES, DISTINCT, SUMMARIZE, ADDCOLUMNS, SELECTCOLUMNS
Ranking Functions TOPN, RANKX
Variables
Variables (VAR RETURN) Make DAX expressions readable, performant, and debuggable
Variables Once-Evaluation A variable is evaluated once; reusing it across the expression saves repeat computation
Variables for Clarity Variables document intermediate steps and make the formula readable
Time Intelligence
Date Table Calendar date table with one row per date covering the full data range
Mark as Date Table Modeling Mark as Date Table; required for time-intelligence DAX
TOTALYTD Year-to-date total against the date table
SAMEPERIODLASTYEAR Same period last year for year-over-year comparison
DATESINPERIOD Trailing window of N months from a reference date
PARALLELPERIOD Same period offset by N periods from the current
Common Patterns
Variance to Target Total Sales minus Target Sales
Variance Percent DIVIDE Variance by Target Sales
Running Total Pattern CALCULATE with DATESYTD pattern
Percent of Total Pattern DIVIDE by CALCULATE with REMOVEFILTERS pattern
Distinct Customer Count DISTINCTCOUNT of the customer ID
Average Order Value DIVIDE Total Sales by Order Count
Days Since Last Purchase DATEDIFF from MAX last-order date to today
Top 5 by Sales CALCULATE with TOPN and VALUES
Performance and Best Practices
Measure Library Discipline Maintain a dedicated Measures table with consistent naming and documentation
Prefer Measures over Calculated Columns Columns inflate the model; measures compute on demand
Use Variables Avoid repeat evaluation of the same sub-expression
Minimise Context Transitions Each context transition is computationally expensive on large models
Single-Direction Relationships Bidirectional filtering is rarely needed and almost always slows the model
Avoid Implicit Measures Explicit measures are clearer and faster than implicit aggregations
Use Star Schema DAX is engineered for star schemas; flat or snowflake models perform worse
Performance Analyzer View Performance Analyzer measures DAX query times per visual
DAX Studio External free tool for deep query tuning, server timings, and execution plans
Common Pitfalls
Calculated Column Where Measure Should Be Pitfall of duplicating per row when a measure would suffice; model bloats and aggregation is wrong
Implicit Measure Surprise Pitfall of fields dragged to Values aggregating as Sum when meaningful sum is impossible
Row Context Forgotten Pitfall of expecting row context when filter context is active or vice versa
No Date Table Pitfall of time-intelligence DAX returning blank because date table is missing or unrelated
Bidirectional Relationships Pitfall of bidirectional filters creating ambiguity and performance problems
Many Filters Not Wrapped in FILTER Pitfall of complex filter logic in CALCULATE not wrapped in FILTER for clarity
DIVIDE Forgotten Pitfall of plain division returning infinity or error; DIVIDE is safer
Variables Mutated Pitfall of mistaking a variable for a re-evaluable expression
No Measure Documentation Pitfall of twenty measures with cryptic names that no analyst can maintain