Power BI DAX Fundamentals: Measures, Calculated Columns, and Evaluation Context

DAX (Data Analysis Expressions) is the formula language used in Power BI, Analysis Services, and Excel Power Pivot. Its syntax resembles Excel functions, and that resemblance is initially helpful and later misleading. Excel formulas are evaluated in the context of a cell. DAX measures are evaluated in the context of a filter — a filter that changes based on where the measure is placed in a report and what slicers are applied. Understanding evaluation context is not optional for writing correct DAX; it is the foundation.

Row Context vs Filter Context

Every DAX formula is evaluated in a context. There are two types.

**Row context** is the context that exists when a formula is evaluated row by row. Calculated columns use row context — the formula is evaluated once per row, with the current row's values available directly. When you create a calculated column 'Total Price' as [Quantity] * [Unit Price], DAX evaluates that formula for each row, substituting that row's values.

**Filter context** is the context that exists when a measure is evaluated. A measure is not evaluated row by row — it is evaluated in the context of the filters applied to the data model. When a measure SUM(Sales[Revenue]) is placed in a matrix row for "2024" and column for "North", the filter context includes Year = 2024 and Region = North. DAX evaluates the SUM with those filters applied.

The distinction matters because a measure in a filter context behaves differently from a calculated column in a row context, and mixing up the two is the source of most DAX errors.

CALCULATE: The Most Important DAX Function

CALCULATE is the function that modifies filter context. Every time you need a measure to evaluate in a different context from where it appears — computing a percentage of a different total, comparing to the prior year, showing a value regardless of slicer selection — CALCULATE is the mechanism.

CALCULATE takes a measure and one or more filter arguments:

CALCULATE(SUM(Sales[Revenue]), Year[Year] = 2024)

This evaluates SUM(Sales[Revenue]) in a context where Year is filtered to 2024, regardless of whatever year filter is applied in the visual. The filter argument overrides (not adds to) the filter context for the Year column.

CALCULATE with multiple filter arguments applies all filters:

CALCULATE(SUM(Sales[Revenue]), Year[Year] = 2024, Region[Region] = "North")

The filters are AND'd together: revenue where Year = 2024 AND Region = North.

CALCULATE with ALL() removes a filter:

CALCULATE(SUM(Sales[Revenue]), ALL(Region))

This computes total revenue ignoring any Region filter — useful for "percent of total by region" where the denominator is total across all regions.

CALCULATE with ALLEXCEPT() removes all filters except specified columns:

CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(Sales, Sales[CustomerID]))

This removes all filters from the Sales table except for CustomerID — computing total revenue per customer regardless of date or product filters.

Measures vs Calculated Columns

The choice between a measure and a calculated column is consequential.

**Calculated columns** are computed at data refresh time, stored in the model, and take up memory. They are evaluated in row context. They are appropriate for values that do not change based on filter context — a categorisation of each row, a formatted string, a flag derived from row-level conditions.

**Measures** are computed at query time, evaluated in filter context, and are not stored. They are appropriate for aggregations and calculations that depend on the current filter context — revenue, count of customers, average order value, year-over-year growth.

The most common mistake is creating a calculated column for something that should be a measure. If you need "revenue per region" as a metric in a visual, you need a measure — the value changes based on which region the user selects. If you create a calculated column 'Revenue', it cannot be a context-dependent aggregation; it is a row-level value that cannot respond to filter changes.

Time Intelligence Functions

DAX has a rich set of time intelligence functions for period-over-period comparisons. They require a properly configured date table — a table with one row per date, covering the full date range of the data, marked as a date table in the model.

**DATEADD**: shifts a date range by a specified number of intervals.

Prior Year Revenue = CALCULATE(

SUM(Sales[Revenue]),

DATEADD(DateTable[Date], -1, YEAR)

)

This computes revenue for the same date range as the current filter, shifted one year back.

**SAMEPERIODLASTYEAR**: a simplified wrapper around DATEADD for the common year-over-year case:

PY Revenue = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(DateTable[Date]))

**DATESYTD**: the year-to-date date range from the beginning of the fiscal year through the current date.

YTD Revenue = CALCULATE(SUM(Sales[Revenue]), DATESYTD(DateTable[Date]))

Use DATESYTD with a year-end date argument if your fiscal year does not end on December 31:

CALCULATE(SUM(Sales[Revenue]), DATESYTD(DateTable[Date], "3/31"))

**DATESINPERIOD**: a date range of a specified length ending at a specified date. Useful for rolling windows:

Revenue Last 30 Days = CALCULATE(

SUM(Sales[Revenue]),

DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -30, DAY)

)

FILTER and Iterator Functions

FILTER applies a row-by-row condition to a table and returns the filtered table. It creates row context, which means you can use column references directly:

CALCULATE(

SUM(Sales[Revenue]),

FILTER(Sales, Sales[Revenue] > 1000)

)

This computes revenue for only transactions above $1,000.

FILTER is a table function — it returns a table, not a scalar. Use it inside aggregation functions or as a CALCULATE filter argument.

Iterator functions (SUMX, AVERAGEX, MINX, MAXX, COUNTX) evaluate an expression for each row of a table and aggregate the results. They create row context:

Total Margin = SUMX(Sales, Sales[Revenue] - Sales[Cost])

This computes revenue minus cost for each row, then sums those row-level margins. This is different from SUM(Sales[Revenue]) - SUM(Sales[Cost]), which computes the aggregate difference — both are correct for the overall total, but SUMX is required when the row-level calculation involves non-additive metrics.

ALL, ALLEXCEPT, and Proportion Calculations

Proportion of total is one of the most common DAX patterns:

Revenue % of Total =

DIVIDE(

SUM(Sales[Revenue]),

CALCULATE(SUM(Sales[Revenue]), ALL(Sales))

)

The numerator is the revenue in the current filter context (whatever the visual is showing). The denominator uses ALL(Sales) to remove all filters from the Sales table, giving the grand total. DIVIDE handles the division safely (returns blank rather than error on division by zero).

For "percent of category" (instead of percent of grand total):

Revenue % of Category =

DIVIDE(

SUM(Sales[Revenue]),

CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(Sales, Product[Category]))

)

ALLEXCEPT removes all filters except Category — the denominator is total revenue for the current category, regardless of product or other filters.

Common DAX Mistakes

**Using a column reference instead of a measure in filter context.** [Revenue] in a measure refers to a column named Revenue, not the measure SUM([Revenue]). Always be explicit about whether you mean the column or a measure.

**Forgetting that CALCULATE is required for filter modification.** ANY filter context modification requires CALCULATE. You cannot conditionally include or exclude data without it.

**Creating calculated columns that should be measures.** Calculated columns do not respond to slicer or visual filter changes.

**Incorrect date table configuration.** Time intelligence functions fail silently or produce wrong results if the date table is not marked as a date table and does not have continuous dates covering the data range.

Our managed BI services and BI strategy practice supports Power BI environments — contact us to discuss your Power BI development and optimisation requirements.

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