Lesson 6 of 11 · 10 min · DAX

Filter context and CALCULATE

Covers: DAX in Power BI Desktop. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • What filter context is and where it comes from
  • The difference between filter context and row context
  • How CALCULATE changes the filter context
  • Use ALL, ALLEXCEPT, REMOVEFILTERS, FILTER and VALUES
  • Build percentage of total, share of category and "ignore the slicer" measures

In simple words

A DAX measure does not have a fixed answer. The same formula "total sales" shows 5,00,000 for the whole company, 80,000 for Pune and 12,000 for Pune in March, depending on which filters are active in that part of the chart. Those active filters are the filter context. CALCULATE is the tool that lets a measure change the filters on purpose: "total sales, but only for paid orders" or "total sales for all cities, ignoring the city slicer".

Words to know

  • Filter context: the set of filters that apply when a measure is evaluated for one cell of a visual.
  • Row context: the "current row" while a formula runs row by row, such as in a calculated column or an iterator.
  • CALCULATE: the DAX function that evaluates an expression in a modified filter context.
  • Context transition: turning the current row into filters when CALCULATE runs inside a row context.

Filter context

Take Total Sales = SUM ( Sales[Amount] ) in a table visual with City on rows and Year on columns. For the cell "Pune, 2025", the filter context is City = Pune AND Year = 2025. Those filters come from:

  • the rows and columns of the visual (each cell has its own),
  • slicers and page, report and visual-level filters,
  • filters passed through relationships from dimension tables to the fact table.

DAX then filters the Sales table to those rows and sums Amount. Change any slicer and every cell is recalculated with a new context. Nothing in the formula mentions Pune or 2025; the context supplies them.

Row context

When a formula runs row by row, as in a calculated column or inside an iterator like SUMX, it has a row context: the current row of one table, so you can refer to its columns (Sales[Quantity] * Sales[UnitPrice]). Row context does not filter other tables on its own; to follow a relationship from the many side to the one side you use RELATED, and from the one side to the many side you use RELATEDTABLE or an aggregation.

These two contexts are the heart of DAX. A measure always runs in filter context. An iterator creates row context on top of it.

CALCULATE

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

It evaluates the expression after modifying the filter context with the filters you list. A simple filter argument is a column test:

Paid Sales =
CALCULATE ( [Total Sales], Sales[Status] = "Paid" )

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

Pune Sales =
CALCULATE ( [Total Sales], Customer[City] = "Pune" )

Three rules to remember:

  1. A boolean filter like Sales[Status] = "Paid" is shorthand for FILTER ( ALL ( Sales[Status] ), Sales[Status] = "Paid" ): it replaces any existing filter on that same column.
  2. Filters on different columns combine with AND.
  3. CALCULATE first computes its filter arguments, then applies them to the context, then evaluates the expression.

So CALCULATE ( [Total Sales], Customer[City] = "Pune" ) in a visual with a City slicer set to Hyderabad returns Pune's sales, because the explicit filter replaced the slicer's.

Removing and keeping filters

To ignore filters, use ALL (or the clearer REMOVEFILTERS inside CALCULATE):

Sales All Cities =
CALCULATE ( [Total Sales], ALL ( Customer[City] ) )

Sales All Customers =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer ) )

ALLEXCEPT ( Table, Column1, ... ) removes every filter on a table except the columns listed, and ALLSELECTED keeps the filters from slicers and the outer page but ignores the visual's own row and column filters (used for "percent of what is shown").

Percentage of total, the most-requested measure:

Sales % of Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customer ) ) )

Sales % of Category =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) )
)

Sales % of Selected =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED () ) )

In a bar chart of cities, each bar's denominator is the total for all customers, regardless of the bar's own city.

FILTER and VALUES

Use FILTER when the condition involves more than one column or a measure, because simple column tests cannot use measures:

Sales of Big Customers =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Customer[CustomerKey] ), [Total Sales] > 50000 )
)

FILTER ( table, condition ) goes through the table row by row (row context) and keeps rows where the condition is true. VALUES ( column ) returns the distinct values currently visible in the column (respecting filters). Passing an entire table to FILTER (FILTER ( Sales, ... )) iterates every fact row and is slow; filter a column or a small dimension instead.

Other helpers: SELECTEDVALUE ( Column, "default" ) returns the single selected value or a default, handy for titles ("Sales for Pune"); HASONEVALUE; ISFILTERED; COUNTROWS ( VALUES ( ... ) ).

Context transition

When CALCULATE (or a measure reference, which has an implicit CALCULATE) runs inside a row context, the current row's column values are converted into filters. This is context transition:

Customer Rank by Sales =
RANKX ( ALL ( Customer[Name] ), [Total Sales] )

Sales per Customer Column =        // in a calculated column on Customer
CALCULATE ( SUM ( Sales[Amount] ) )

In the calculated column on the Customer table, CALCULATE turns the current customer row into a filter, so the sum is that customer's sales. A bare SUM ( Sales[Amount] ) in the same column would return the grand total for every row. Referring to an existing measure [Total Sales] has the same effect, which is why iterating a table and calling a measure inside it works.

A worked example

With a slicer set to Year 2025 and a chart by City, the cell for Pune:

Measure Evaluates to
[Total Sales] sales in Pune and 2025
CALCULATE ( [Total Sales], Customer[City] = "Hyderabad" ) Hyderabad's sales (the City filter is replaced), still 2025
CALCULATE ( [Total Sales], ALL ( Customer[City] ) ) all cities, still 2025
CALCULATE ( [Total Sales], ALL ( 'Date' ) ) Pune, all years (the year slicer is removed)
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customer[City] ) ) ) Pune's share of 2025 sales

Debugging

  • Put a measure in a table visual with the dimensions to see what it returns for each combination.
  • Break a complex measure into pieces with VAR, and return each variable in turn.
  • Use the DAX query view and EVALUATE to inspect tables, or Performance analyzer to see which visual is slow.
  • Remember relationships: a filter on a table reaches the measure only if there is a path.

Common mistakes

  • Expecting a measure to have a fixed answer instead of depending on context.
  • Forgetting that CALCULATE's boolean filters replace existing filters on the same column.
  • Using FILTER over a whole fact table where a column filter would do.
  • Using ALL on a table and breaking slicers you meant to keep.
  • Summing a column in a calculated column without CALCULATE and getting the grand total.

Try it yourself

  1. Create Paid Sales and Paid % (paid sales divided by total sales) and chart them by month.
  2. Create Sales % of Total and show it in a bar chart by city with a City slicer; try ALL versus ALLSELECTED.
  3. Write a measure that ignores the Year slicer using REMOVEFILTERS ( 'Date'[Year] ).
  4. Build Sales of Big Customers with FILTER ( VALUES ( ... ), ... ) and check one customer by hand.
  5. Use SELECTEDVALUE to make a card title that changes with a slicer.

Check yourself

What is filter context?

The set of filters (from rows, columns, slicers, filters and relationships) that apply when a measure is evaluated for a particular cell of a visual.

What does CALCULATE ( [Total Sales], Sales[Status] = "Paid" ) do if a Status slicer is set to "Pending"?

It replaces the slicer's filter on Status with "Paid" and returns paid sales.

How do you calculate the percentage of the grand total?

Divide the measure by the same measure calculated with the relevant filters removed, such as CALCULATE ( [Total Sales], ALL ( Customer ) ).

What is context transition?

When CALCULATE or a measure runs inside a row context, the current row's values become filters so the calculation is for that row.

Interview questions

Explain filter context and row context.

Filter context is the set of filters applied to the model for a given evaluation, such as a chart cell and slicers. Row context is the current row during row-by-row evaluation, as in calculated columns and iterators. Measures run in filter context; iterators add row context.

What is the difference between ALL, ALLEXCEPT and ALLSELECTED?

ALL removes filters from a table or columns; ALLEXCEPT removes all except the columns listed; ALLSELECTED removes the visual's own filters but keeps slicer and page filters.

Why is CALCULATE so important in DAX?

It is the only function that can change the filter context, which makes comparisons, percentages of total, time intelligence and many business measures possible.

Summary

  • Measures are evaluated in a filter context from visuals, slicers, filters and relationships.
  • CALCULATE evaluates an expression in a modified context; its column filters replace existing ones.
  • ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS remove filters; FILTER and VALUES build conditions.
  • Context transition turns a row into filters; use it deliberately.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.