DAX Guide: Measures, Calculated Columns and Context

DAX, Data Analysis Expressions, is the formula language of Power BI, Analysis Services and Power Pivot in Excel (Microsoft Learn: DAX overview). Its functions look familiar to Excel users, which is exactly what trips them up: DAX does not work on cells, it works on columns and tables, and the same formula returns different results depending on where it is evaluated. Two ideas unlock almost everything else: the difference between measures and calculated columns, and context.

Five places a calculation can live

Microsoft lists five options for adding calculations to a Power BI report (Microsoft Learn: calculation options):

The first three produce stored data; the last two produce answers. That distinction drives the decision.

Measure or calculated column?

Use a calculated column when each row needs a value you will slice, filter or group by: a price band, an age group, a flag for high-value orders. It is computed once per row at refresh and stored, so it adds to the model's size, and it does not respond to filters.

Use a measure when you are aggregating: totals, counts, averages, ratios, anything shown as a value in a visual. It is calculated when a visual asks for it, in the context of each cell, and nothing is stored. Measures can only be used as values and in visual-level filters, not on slicers or axes.

Where a row-level column could be built in either Power Query or DAX, building it in Power Query keeps the transformation with the rest of the data preparation.

The costly mistake, in numbers

Margin is the classic case where the wrong choice gives a plausible but wrong answer. Here is Microsoft's Financial Sample, the workbook of sales and profit Microsoft provides for trying Power BI (Microsoft Learn: Financial Sample), with margin calculated both ways:

-- calculated column: one margin per row, then averaged in visuals
Row Margin = DIVIDE ( Financials[Profit], Financials[Sales] )

-- measure: total profit divided by total sales in each cell's context
Margin = DIVIDE ( SUM ( Financials[Profit] ), SUM ( Financials[Sales] ) )

import io
import urllib.request
import pandas as pd

url = "https://go.microsoft.com/fwlink/?LinkID=521962"
finance = pd.read_excel(io.BytesIO(urllib.request.urlopen(url).read()), keep_default_na=False)
finance.columns = finance.columns.str.strip()
finance["Row Margin"] = finance["Profit"] / finance["Sales"]            # the calculated column

print(f"{len(finance)} rows | average of Row Margin: {finance['Row Margin'].mean():.1%} "
      f"| Margin measure: {finance['Profit'].sum() / finance['Sales'].sum():.1%}")
by_product = finance.groupby("Product").agg(Profit=("Profit", "sum"), Sales=("Sales", "sum"),
                                            avg_row_margin=("Row Margin", "mean"))
by_product["Margin measure"] = (by_product["Profit"] / by_product["Sales"]).map("{:.1%}".format)
by_product["Average of Row Margin"] = by_product["avg_row_margin"].map("{:.1%}".format)
print(by_product[["Average of Row Margin", "Margin measure"]].to_string())

700 rows | average of Row Margin: 27.9% | Margin measure: 14.2%
          Average of Row Margin Margin measure
Product                                        
Amarilla                  29.0%          15.9%
Carretera                 29.5%          13.2%
Montana                   27.2%          13.7%
Paseo                     28.6%          14.5%
VTT                       26.4%          14.8%
Velo                      26.5%          12.6%

Averaging the stored column reports a margin of 27.9%, nearly double the true 14.2%, and it does so for every product. The average treats a small sale with a high margin exactly like a large sale with a thin one. The measure divides total profit by total sales in whatever context it is evaluated, so it is right for every product, segment, month and grand total. Ratios, averages of amounts and percentages almost always belong in measures.

Row context and filter context

Microsoft's overview defines the contexts DAX works with:

• Row context is "the current row". A calculated column is evaluated once per row, and a formula such as Financials[Profit] / Financials[Sales] automatically uses the values in that row. Row context also follows relationships, so RELATED can fetch a value from the related row of another table.
• Filter context is the set of values allowed in each column. Every cell of a visual has its own: the row and column headers, slicers, and page and report filters. A measure is evaluated separately for each cell, which is why one formula shows a different value in every cell.

A calculated column has row context but no filter context, so SUM ( Financials[Profit] ) in a column returns the grand total on every row. A measure has filter context but no row context, so it cannot refer to "this row's" value directly. The bridge between the two is CALCULATE, which turns a row context into a filter context; DAX CALCULATE explained works through it step by step.

Iterators: row context inside a measure

Some calculations need a row-by-row step inside a measure, such as revenue as quantity times price. Iterator functions, those ending in X such as SUMX and AVERAGEX, create a row context over a table, evaluate an expression for each row, then aggregate:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

This avoids storing a revenue column just to sum it. Iterating a large table has a cost, so iterate the smallest table that gives the right answer.

Habits for reliable measures

• Create explicit measures for everything shown, even simple sums, rather than relying on automatic summarisation.
• Build on base measures. Define Revenue once and reuse [Revenue] in margin, growth and share measures.
• Use DIVIDE for ratios, so a zero denominator returns blank instead of an error.
• Store intermediate results in variables for readability and speed; see DAX variables.
• Name measures for what they mean, such as "Gross Margin %", and set their format.

Where to go next

Once measures and context are clear, the rest of DAX builds on them: FILTER and ALL for controlling filters, time intelligence in DAX for year-to-date and prior-year comparisons, calculation groups for reusing patterns across measures, and the top 20 DAX functions for the vocabulary most reports need.

DAX fundamentals, aggregate functions, calculated columns and measures are part of the Power BI course.

Sources

• DAX overview (Microsoft Learn) — Microsoft Learn
• Use calculation options in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Download the Financial Sample Excel workbook for Power BI (Microsoft Learn) — Microsoft Learn