Skip to content
FR

DAX Fundamentals and CALCULATE

Step 9 of 24

DAX Fundamentals and CALCULATE

Introduction

This lesson continues the domain Model the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). Data Analysis Expressions (DAX) is the formula language of Power BI. The exam expects you to write and read single-aggregation measures, understand how CALCULATE changes filter context, use basic statistical functions, use quick measures, and create calculated tables and columns. Questions typically show a scenario and several formulas and ask which returns the required result, or why a total looks wrong. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to write measures with the common aggregators, explain row context and filter context, modify filters with CALCULATE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS and KEEPFILTERS, use iterators such as SUMX, and decide when a quick measure is enough.

Learning objectives

  • Create explicit measures with SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS and DISTINCTCOUNT.
  • Distinguish row context from filter context and explain context transition.
  • Use CALCULATE with Boolean filters, table filters and filter-removal functions.
  • Use iterators (SUMX, AVERAGEX) and basic statistical functions (MEDIAN, STDEV.S, PERCENTILE.INC, RANKX).
  • Use DIVIDE, variables and quick measures appropriately.
  • Create calculated tables and columns with the right functions (RELATED, ADDCOLUMNS, SUMMARIZE).

1. Measures: the basics

A measure is a DAX formula evaluated at query time in the filter context of each cell of a visual. You create it with New measure (Home or Table tools) and write Name = expression.

Total Sales = SUM ( FactSales[Amount] )
Total Quantity = SUM ( FactSales[Qty] )
Order Count = DISTINCTCOUNT ( FactSales[OrderID] )
Average Price = AVERAGE ( FactSales[UnitPrice] )
Lines = COUNTROWS ( FactSales )
Function Returns Notes
SUM Sum of a numeric column Only numeric columns
AVERAGE Arithmetic mean of a column Ignores blanks
MIN, MAX Smallest and largest value Work on numbers, dates and text
COUNT Number of non-blank numeric values Numeric columns only
COUNTA Non-blank values of any type
COUNTROWS Number of rows in a table Very common and fast
DISTINCTCOUNT Number of distinct values Includes blank as one value
DIVIDE ( a, b, [alt] ) Division returning blank (or alt) on divide by zero Prefer to / when the denominator can be zero

Best practices:

  • Write explicit measures rather than dragging numeric columns onto visuals (implicit measures), because implicit ones cannot be reused in other measures and are not available in some tools.
  • Put measures in a dedicated table or in a table where they belong, using display folders.
  • Reuse measures in other measures: Margin % = DIVIDE ( [Profit], [Total Sales] ).
  • Format measures (currency, percent) with the Measure tools ribbon.

2. Row context and filter context

Understanding these two ideas is the key to DAX.

Filter context is the set of filters that apply to a calculation: slicers, visual axes, page and report filters, filters from relationships and filters added by CALCULATE. In a matrix with Category on rows and Year on columns, each cell has its own filter context (for example Category = Bikes and Year = 2025), and SUM ( FactSales[Amount] ) is evaluated inside it.

Row context is the "current row" in a table that DAX is iterating. It exists in a calculated column (each row of the table) and in iterator functions (SUMX, AVERAGEX, FILTER, ADDCOLUMNS). Row context alone does not filter other tables; to read a related value use RELATED (many to one) or RELATEDTABLE (one to many).

Context transition happens when CALCULATE (or a measure reference) is evaluated inside a row context: the current row is converted into equivalent filters. That is why a measure used in a calculated column returns a different value for each row, whereas a bare SUM in a calculated column returns the same grand total for every row.

-- Calculated column on DimCustomer
Customer Sales = SUM ( FactSales[Amount] )          // same value for all rows (no context transition)
Customer Sales (correct) = CALCULATE ( SUM ( FactSales[Amount] ) )  // per customer through context transition

Evaluation order of the rule: relationships propagate filters from the one side to the many side, so the filter on DimCustomer[Name] filters FactSales, and SUM on the fact respects it.

3. CALCULATE

CALCULATE is the most important DAX function:

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

It evaluates the expression in a modified filter context. The filters you supply either add to the existing context, replace the filters on a column, or remove filters, depending on their form.

Boolean filter arguments

Bike Sales = CALCULATE ( [Total Sales], DimProduct[Category] = "Bikes" )

A Boolean filter on one column is shorthand for a table filter that returns the values of that column satisfying the condition (FILTER ( ALL ( DimProduct[Category] ), DimProduct[Category] = "Bikes" )). Important consequence: it replaces any existing filter on DimProduct[Category]. If a slicer selects "Accessories", Bike Sales still shows Bikes sales, because the CALCULATE filter overrides the slicer on that column. Other filters (Year, Region) remain.

Table filter arguments with FILTER

Use FILTER when the condition involves several columns, a measure, or an expression that cannot be a simple Boolean on one column:

Large Orders Sales =
CALCULATE ( [Total Sales], FILTER ( FactSales, FactSales[Amount] > 1000 ) )

FILTER is an iterator; on large tables a column filter is faster than iterating the whole fact table. A condition comparing a measure, such as customers with sales above 10,000, requires FILTER over a column or table that provides the row context.

Removing and keeping filters

Function Effect
ALL ( Table ) / ALL ( Column ) Removes all filters from the table or column; returns all rows or values
ALLEXCEPT ( Table, Col1, ... ) Removes all filters on the table except the listed columns
REMOVEFILTERS ( ... ) Clearer synonym for removing filters inside CALCULATE
ALLSELECTED ( ... ) Removes filters from the visual but keeps those from slicers and outer filters (the "visible total")
VALUES ( Column ) Distinct values of a column visible in the current context
KEEPFILTERS ( ... ) Intersects the new filter with existing ones instead of replacing them 
All Products Sales = CALCULATE ( [Total Sales], REMOVEFILTERS ( DimProduct ) )
Share of All Products = DIVIDE ( [Total Sales], [All Products Sales] )
Share of Category =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( DimProduct, DimProduct[Category] ) ) )

Share of All Products shows each product's percentage of the grand total, ignoring product filters but respecting Year and Region. ALLSELECTED is used for the percentage of the visible total (respecting slicers).

Order of evaluation inside CALCULATE. The existing filter context is first modified by the CALCULATE arguments (applied together, with removal and replacement logic), then the expression is evaluated. Filters in the arguments are combined with AND across different columns. To express OR across columns use FILTER with ||, or UNION of tables.

CALCULATE with USERELATIONSHIP and other modifiers

CALCULATE also accepts USERELATIONSHIP (inactive relationships), CROSSFILTER (change direction for the calculation) and TREATAS (apply the values of one table as filters on columns of another). These appear again in the next lesson.

4. Iterators and row-by-row calculations

An iterator (suffix X) evaluates an expression for each row of a table and then aggregates:

Total Revenue = SUMX ( FactSales, FactSales[Qty] * FactSales[UnitPrice] )
Avg Order Value = AVERAGEX ( VALUES ( FactSales[OrderID] ), [Total Sales] )
Max Line Value = MAXX ( FactSales, FactSales[Qty] * FactSales[UnitPrice] )

Use SUMX when the amount must be computed per row first (quantity times price). SUM ( Qty ) * SUM ( UnitPrice ) is wrong: it multiplies totals, not row values. The same logic explains AVERAGEX over VALUES ( OrderID ): average per order requires iterating the orders.

Worked example 1: revenue and average order value. Scenario. FactSales has OrderID, Qty and UnitPrice but no Amount column. Management wants total revenue, and the average revenue per order, in a card and a table by country.

Reasoning.

  1. Total revenue needs row-level multiplication: Total Revenue = SUMX ( FactSales, FactSales[Qty] * FactSales[UnitPrice] ).
  2. An order has several lines, so average order value is total revenue divided by the number of distinct orders: Avg Order Value = DIVIDE ( [Total Revenue], DISTINCTCOUNT ( FactSales[OrderID] ) ).
  3. Place both on a table with DimCustomer[Country]. Relationship filtering applies the country to each calculation.
  4. Check the grand-total row: revenue totals correctly; the average is revenue over all distinct orders (not the average of country averages).

Why alternatives are worse. AVERAGE ( FactSales[UnitPrice] ) gives average line price, not order value. SUM ( Qty ) * AVERAGE ( UnitPrice ) mixes grains and returns a wrong amount. A calculated column Amount = Qty * UnitPrice also works, but stores a new column on millions of rows; the iterator measure avoids that cost.

5. Basic statistical functions

Function Use
MEDIAN ( Column ) Middle value; robust to outliers (typical order value)
STDEV.S, STDEV.P Standard deviation of a sample or a population
VAR.S, VAR.P Variance
PERCENTILE.INC ( Column, k ) / PERCENTILE.EXC Value at the k-th percentile (inclusive or exclusive)
COUNTBLANK, DISTINCTCOUNTNOBLANK Counting blanks, counting distinct without blank
RANKX ( Table, Expression, [Value], [Order], [Ties] ) Rank of an item by an expression
TOPN ( n, Table, Expression, [Order] ) Top n rows by an expression
GEOMEAN, RAND, ABS, ROUND Other numeric helpers 
Median Order Value = MEDIAN ( FactSales[Amount] )
Product Rank = RANKX ( ALL ( DimProduct[Product] ), [Total Sales] )
Top 5 Products Sales = CALCULATE ( [Total Sales], TOPN ( 5, ALL ( DimProduct[Product] ), [Total Sales] ) )

For RANKX, remember to use ALL ( column ) as the table so that the rank is computed among all products, not just the filtered one (a rank over only the current product is always 1).

6. Variables

VAR ... RETURN stores intermediate results, improves readability and avoids recomputing the same expression:

Sales vs Average Product =
VAR CurrentSales = [Total Sales]
VAR AvgSales = CALCULATE ( AVERAGEX ( VALUES ( DimProduct[Product] ), [Total Sales] ), REMOVEFILTERS ( DimProduct ) )
RETURN
    DIVIDE ( CurrentSales - AvgSales, AvgSales )

Variables are evaluated once, in the context where they are defined, not where they are used. This detail matters when a variable is defined outside CALCULATE and then used in it: the variable keeps its original value.

7. Quick measures

Quick measures (Home → Quick measure, or the Data pane context menu) generate DAX from a template: running total, percent difference, year-to-date total, rolling average, weighted average, star rating and more. You fill fields in a dialog, and Power BI writes a measure you can inspect and edit. They are good for getting started and for learning syntax, and the exam may ask what they do, but remember:

  • They create normal measures; there is no special runtime behaviour.
  • They require a suitable model (for example a date column for time-based measures).
  • They can be edited like any measure and should be reviewed for correctness and naming.
  • Copilot can also suggest measures and DAX from a description, but the author remains responsible for checking the result.

8. Calculated tables and columns

Calculated columns run at refresh for each row and are stored. Use RELATED to bring a value from the one side:

Line Margin = FactSales[Amount] - RELATED ( DimProduct[StandardCost] ) * FactSales[Qty]

Calculated tables return a table:

Top Customers = TOPN ( 100, DimCustomer, CALCULATE ( SUM ( FactSales[Amount] ) ) )

Sales by Year =
SUMMARIZE ( FactSales, DimDate[Year], "Revenue", SUM ( FactSales[Amount] ) )

Product Lookup =
ADDCOLUMNS ( VALUES ( DimProduct[Product] ), "Sales", [Total Sales] )
Function Purpose
ADDCOLUMNS ( Table, "Name", Expression ) Adds columns computed per row
SUMMARIZE Groups a table and optionally adds aggregations
SELECTCOLUMNS Picks or renames columns
DISTINCT, VALUES Unique values
UNION, INTERSECT, EXCEPT Set operations on tables
DATATABLE, { } constructor Small hard-coded tables (for example a what-if list)

Reminders: calculated tables are evaluated at refresh and do not change with slicers. A what-if parameter (Modeling → New parameter) generates a calculated table and a measure for a slicer-driven scenario value.

9. Pitfalls and exam tips

  • A bare SUM in a calculated column gives the same total on every row; use RELATED, CALCULATE or an iterator as appropriate.
  • A Boolean filter in CALCULATE replaces slicer selections on that column. Use KEEPFILTERS to intersect with them.
  • SUM ( Qty ) * SUM ( Price ) is not the sum of Qty × Price; use SUMX.
  • Use DIVIDE instead of / when the denominator may be zero.
  • Percent-of-total measures need the right removal: ALL ( Table ) ignores slicers on that table too; ALLSELECTED keeps outer selections.
  • COUNT counts numbers only; COUNTROWS and DISTINCTCOUNT are the common choices.
  • Prefer measures over calculated columns for aggregates; prefer variables for readability and speed.
  • Exam tip: if a question says a formula returns the same value on every row of a table visual, suspect missing context transition or a filter removed by ALL.
  • Exam tip: "respect the user's slicers but ignore the visual's grouping" → ALLSELECTED. "Ignore all product filters" → REMOVEFILTERS ( DimProduct ) or ALL.
  • Exam tip: the exam may show a DAX formula and ask for its result; trace the filter context cell by cell.

10. Mini case study

Case. Alpine Gear sells through a FactSales table (OrderID, ProductID, CustomerID, DateKey, Qty, UnitPrice, Discount) related to DimProduct (Product, Category, StandardCost), DimCustomer (Name, Country) and DimDate. Managers ask for: (1) total net revenue after discount; (2) revenue of the Accessories category regardless of a Category slicer, next to the selected one; (3) each product's share of the revenue of its own category; (4) a table of the top 5 products by revenue; and (5) a calculated column on FactSales with gross margin per line.

Tasks. Write the DAX.

Solution.

  1. Net revenue per row needs row-level math: Net Revenue = SUMX ( FactSales, FactSales[Qty] * FactSales[UnitPrice] * ( 1 - FactSales[Discount] ) ).
  2. Accessories Revenue = CALCULATE ( [Net Revenue], DimProduct[Category] = "Accessories" ). The Boolean filter overrides the Category slicer, which is the desired behaviour. A second measure Selected Revenue = [Net Revenue] follows the slicer.
  3. Share in Category = DIVIDE ( [Net Revenue], CALCULATE ( [Net Revenue], ALLEXCEPT ( DimProduct, DimProduct[Category] ) ) ). ALLEXCEPT removes the product filter but keeps the category, so the denominator is the category total.
  4. Top 5: Top 5 Revenue = CALCULATE ( [Net Revenue], TOPN ( 5, ALL ( DimProduct[Product] ), [Net Revenue] ) ). In a table visual, apply a Top N visual filter on product by [Net Revenue] as the simplest alternative.
  5. Gross Margin = FactSales[Qty] * FactSales[UnitPrice] * ( 1 - FactSales[Discount] ) - FactSales[Qty] * RELATED ( DimProduct[StandardCost] ) as a calculated column (row-level, uses RELATED). Alternatively compute margin as a measure with SUMX so that it uses no storage.

Commentary. Items 1 and 3 test row-level iteration and filter removal. Item 2 shows that CALCULATE overrides a slicer on the same column. Item 5 shows when a calculated column is acceptable: row-level, needs RELATED, and the model is small enough.

Key terms

  • Measure: formula evaluated at query time in filter context.
  • Filter context: active filters on the model for a calculation.
  • Row context: the current row during iteration or in a calculated column.
  • Context transition: conversion of row context to filter context by CALCULATE.
  • Iterator (X function): evaluates an expression per row, then aggregates.
  • ALL / ALLEXCEPT / ALLSELECTED / REMOVEFILTERS: filter-removal functions.
  • KEEPFILTERS: keeps existing filters when applying a new one.
  • Variable (VAR): named intermediate value evaluated once.
  • Quick measure: template-generated measure.
  • Calculated table: table created with DAX at refresh.

Summary checklist

  • I can write SUM, COUNTROWS, DISTINCTCOUNT and DIVIDE measures.
  • I can explain row context, filter context and context transition.
  • I can use CALCULATE with Boolean, table and filter-removal arguments.
  • I can choose SUMX instead of multiplying aggregates.
  • I can use MEDIAN, STDEV.S, PERCENTILE.INC, RANKX and TOPN.
  • I can use variables and review quick measures.
  • I can create calculated columns with RELATED and calculated tables with SUMMARIZE or ADDCOLUMNS.

Official sources