DAX Practice
  1. 01
  2. 02
  3. 03

Streak 0 daysPoints 0

Lesson08/59
Row context becoming filter context

Context transition

New functionsFILTER, COUNTROWS
StatusNot started

FILTER(Customer, condition) keeps the customers for which the condition is true. It iterates Customer, so the condition runs in a row context, one customer at a time.

A row context does not filter anything by itself. A raw SUMX(Sales, ...) inside the condition still sees all the sales of the cell, not just this customer's.

A measure reference such as [Sales Amount] is wrapped in an implicit CALCULATE. CALCULATE turns the current row into a filter: this is context transition. Now the condition sees only the current customer's sales.

Your task

Write a measure named Big Customers: the number of customers whose sales amount in the cell is above 5,000.

*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 row context becoming filter context with FILTER and COUNTROWS. 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, Big Customers, fills a Power BI-style matrix grouped by Date[Year] and Customer[Segment]. 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

Big Customers = COUNTROWS(FILTER(Customer, [Sales Amount] > 5000))

Measure references inside an iterator trigger context transition: the current row becomes a filter. That is why [Sales Amount] works where a bare SUMX does not.

The expected result

Expected result for Big Customers
Segment
YearBusinessConsumerTotal
2024426
2025415
Total448

Common mistakes

  • Big Customers = COUNTROWS(FILTER(Customer, SUMX(Sales, Sales[Quantity] * Sales[Unit Price]) > 5000))

    Every customer passes, because the SUMX inside FILTER sees all sales of the cell: a row context does not filter. Use the measure [Sales Amount] (or wrap the SUMX in CALCULATE) to trigger context transition.

  • Big Customers = COUNTROWS(FILTER(Sales, [Sales Amount] > 5000))

    That counts sales lines above 5,000, not customers. Iterate the Customer table.

  • Big Customers = IF([Sales Amount] > 5000, DISTINCTCOUNT(Sales[CustomerKey]))

    That tests the whole cell's sales once. The test has to run per customer: COUNTROWS(FILTER(Customer, ...)).

Hints

  • COUNTROWS(FILTER(Customer, ...)) counts the customers that pass a test.
  • Inside FILTER, compare the measure [Sales Amount] with 5000.

All DAX lessons

Opening the exercise…