DAX Practice
  1. 01
  2. 02
  3. 03

Streak 0 daysPoints 0

Lesson05/59
Changing the filter context with CALCULATE

CALCULATE with a filter

New functionsCALCULATE
StatusNot started

CALCULATE(expression, filter, ...) evaluates an expression in a modified filter context. It is the most important function in DAX.

A filter such as Product[Category] = "Bikes" replaces any existing filter on Product[Category] and keeps every other filter. In this matrix, Country and Year still filter the result.

Your task

Write a measure named Bike Sales: the sales amount of the Bikes category only.

*1*1*1*1SALEST1 · 420 ROWSSalesKeyNUMOrder DateDATEShip DateDATEProductKeyNUMCustomerKeyNUMQuantityNUMUnit PriceNUMPRODUCTT2 · 8 ROWSProductKeyNUMProductTEXTCategoryTEXTColorTEXTList PriceNUMStandard CostNUMCUSTOMERT3 · 10 ROWSCustomerKeyNUMCustomerTEXTCountryTEXTSegmentTEXTDATET4 · 731 ROWSDateDATEYearNUMQuarterTEXTMonthTEXTMonth NumberNUMNOTES1Filters flow from 1 to *, alongthe arrow: Product filters Sales.2Select a column or a measureto insert it at the cursor.3Measures in this model:[Sales Amount][Total Cost]Key columnFact tableSALES MODEL · STAR SCHEMASHEET 1 OF 1

About this exercise

You practise changing the filter context with calculate with CALCULATE. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.

Your measure, Bike Sales, fills a Power BI-style matrix grouped by Customer[Country] and Date[Year]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.

Measures that already exist in the model: Sales Amount and Total Cost. Reference them in square brackets, for example [Sales Amount].

Last updated 10 November 2026

Solution, expected result and common mistakes

Worked solution

Bike Sales = CALCULATE([Sales Amount], Product[Category] = "Bikes")

CALCULATE filters replace the filter on the same column and leave the other filters of the cell alone.

The expected result

Expected result for Bike Sales
Year
Country20242025Total
France25,203.5017,523.0042,726.50
Germany29,732.5045,454.5075,187.00
Netherlands18,575.0024,948.0043,523.00
United Kingdom28,853.5019,316.0048,169.50
Total102,364.50107,241.50209,606.00

Common mistakes

  • Bike Sales = CALCULATE([Sales Amount], Product[Product] = "Bikes")

    There is no product called "Bikes": Bikes is a value of Product[Category].

  • Bike Sales = CALCULATE([Sales Amount], ALL(Customer), Product[Category] = "Bikes")

    ALL(Customer) removes the country filter, so every row shows the same total. Keep only the category filter.

Hints

  • CALCULATE([Sales Amount], ...): the second argument is a filter.
  • The filter compares a column with a value: Product[Category] = "Bikes".

All DAX lessons

Opening the exercise…