DAX Basics: Measures, Calculated Columns and Context

DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot and Analysis Services. To use it well you need three ideas: measures (calculations evaluated on the fly for each cell of a visual), calculated columns (values stored row by row in a table), and context (the filters and current row that decide what a formula actually sees). Get these right and most DAX "mysteries" disappear.

What DAX looks like

DAX looks a lot like Excel formulas, and many functions share names: SUM, AVERAGE, IF, DIVIDE, YEAR. The big difference is that DAX never refers to cells like A2. It refers to tables and columns, written as TableName[ColumnName], and to other measures, written as [MeasureName].

Total Sales = SUM ( Sales[SalesAmount] )

The part before the equals sign is the name; the part after is the expression. In the examples below we'll use a simple model: a Sales table (OrderDate, ProductKey, Quantity, UnitPrice, UnitCost), a Product table (ProductKey, ProductName, Category) and a Calendar table, connected with one-to-many relationships.

If you haven't built a model like this yet, start with your first Power BI report in 60 minutes.

Measures: calculations that respond to filters

A measure is a formula that isn't stored anywhere. Power BI evaluates it every time a visual needs a number, using whatever filters apply to that cell. The same measure shows total revenue on a card, revenue per category in a bar chart, and revenue per month in a line chart, all without changes.

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

Cost = SUMX ( Sales, Sales[Quantity] * Sales[UnitCost] )

Profit = [Revenue] - [Cost]

Profit Margin % = DIVIDE ( [Profit], [Revenue] )

Notice the building-block style: Profit reuses Revenue and Cost, and Profit Margin % reuses Profit. If you later fix how Revenue is calculated, everything built on it updates.

SUM vs SUMX

SUM adds up one column. SUMX is an iterator: it goes row by row through a table, evaluates an expression for each row (here, quantity times price), then adds the results. Other "X" functions work the same way: AVERAGEX, MINX, MAXX, COUNTX.

Good measure habits

• Use DIVIDE instead of the / operator; it returns blank (or an alternate value you choose) when dividing by zero.
• Write explicit measures even for simple sums, rather than dragging a numeric column into a visual. Explicit measures are reusable and easier to format.
• Keep measures in one place, such as a dedicated empty "Measures" table, so they're easy to find.
• Use variables (VAR … RETURN) to make longer formulas readable.

Profit Margin % =
VAR Rev = [Revenue]
VAR Prof = [Profit]
RETURN
    DIVIDE ( Prof, Rev )

Calculated columns: values stored per row

A calculated column adds a new column to a table. Its formula runs once for every row when the data is refreshed, and the results are stored in the model, just like imported columns.

Line Revenue = Sales[Quantity] * Sales[UnitPrice]

Price Band =
IF ( Sales[UnitPrice] >= 50, "Premium", "Standard" )

Category = RELATED ( Product[Category] )

RELATED pulls a value from the "one" side of a relationship into the "many" side, similar to a lookup. Calculated columns are useful when you need to slice, filter or group by the result: Price Band can go on an axis or in a slicer, and a measure can't.

Measures vs calculated columns: which one to use

A simple rule: if you'll aggregate it, write a measure; if you'll filter or group by it, use a column. And if a column can be created in Power Query instead, that's often the better place, since Power Query columns tend to compress well and keep logic in one layer.

Context: why the same formula gives different answers

Context is the part that confuses most beginners. Every DAX formula is evaluated somewhere: in a specific row of a table, or in a specific cell of a visual. That "somewhere" is the context, and it decides which rows the formula can see. There are two kinds.

Row context

Row context means "the current row." It exists inside calculated columns and inside iterators like SUMX. When a calculated column says Sales[Quantity] * Sales[UnitPrice], DAX knows which row's quantity and price to use because it's evaluating one row at a time.

A measure on its own has no current row, which is why this fails as a measure:

Bad Measure = Sales[Quantity] * Sales[UnitPrice]   -- error: no row context

Wrapping it in SUMX creates the row context it needs. Iterators are how measures do row-by-row math, and they're the reason you rarely need helper columns.

Filter context

Filter context is the set of filters active when a measure is evaluated. It comes from:

• The rows and columns of the visual (for example, Category = "Bikes" and Year = 2025 in a matrix cell).
• Slicers and cross-filtering from other visuals.
• Filters in the Filters pane (visual, page and report level).
• Filter changes made inside a formula with CALCULATE.

Filters also travel along relationships: a filter on Product[Category] flows to the Sales table through the ProductKey relationship, so [Revenue] only sums sales of that category. That's why a star-shaped model matters; see star schema in Power BI.

A worked example

Put [Revenue] in a matrix with Category on rows and Year on columns. For the cell "Bikes / 2025," the filter context is Category = Bikes and Year = 2025. Power BI filters Sales accordingly and runs SUMX over just those rows. The Total row has no Category filter, so it sums all categories for that year. One formula, many answers, each correct for its context.

Changing context with CALCULATE

CALCULATE evaluates an expression in a modified filter context. For example, revenue for Bikes regardless of what the visual says about category:

Bikes Revenue = CALCULATE ( [Revenue], Product[Category] = "Bikes" )

CALCULATE is the most important function in DAX and deserves its own article: the CALCULATE function explained simply.

Next steps

Open a model and write five measures: Revenue, Cost, Profit, Profit Margin % and Orders. Put them in a matrix with Category and Year, and for each cell say out loud what the filter context is. Then create one calculated column (a price band or a flag) and use it in a slicer. When that feels comfortable, move on to CALCULATE and then to time intelligence such as year-to-date and year-over-year comparisons.

Watch: video lessons

▶

▶

Frequently asked questions