Level 4 · Filter by a measure with FILTER

A boolean filter such as orders[status] = "completed" tests a column. Jessica’s condition is different: an order counts when its total is above $500, and the total is a measure computed from many order lines. For that you need a table filter built with FILTER.

What you are working in

This page runs a practice DAX engine built for this site. It evaluates your measure in each cell of the matrix and checks it. In Power BI Desktop: Home → New measure, type the formula, and add a slicer on 'Date'[Year Month] set to 2025-12 next to the matrix.

FILTER returns a table

FILTER(VALUES(orders[order_id]), [Sales] > 500)

• VALUES(orders[order_id]) returns the order ids visible in the current filter context: one channel, one month. It is a one-column table.
• FILTER(table, condition) is an iterator: it evaluates the condition once per row and keeps the rows where it is true.
• [Sales] > 500 is evaluated for one order at a time. When a measure is called inside an iterator, DAX turns the current row into a filter first (context transition), so [Sales] sees only that order’s lines.

The result is a table of order ids. Passed to CALCULATE, it becomes a filter like any other:

Big Order Sales =
CALCULATE(
    [Sales],
    FILTER(VALUES(orders[order_id]), [Sales] > 500)
)

Why not a boolean filter?

CALCULATE([Sales], [Sales] > 500) is refused: a boolean filter must be about columns, and [Sales] is a measure whose value depends on context. And order_items[line_total] > 500 is a valid filter that answers a different question: it keeps expensive lines, so a $550 order made of two cheaper items drops out.

Iterate the smallest table

FILTER does real work for every row it visits. Here it visits at most about 330 December orders per cell. FILTER(order_items, …) with a measure inside would visit every order line, each with its own context transition, which is what makes reports crawl. VALUES of a key column on the “one” side gives the same answer with far fewer steps. The same advice holds in Power BI: never FILTER a whole fact table when a column will do.

Common mistakes

• FILTER(orders, …) instead of VALUES(orders[order_id]). It gives the same number here, but it iterates whole rows and turns every column of orders into a filter. That is heavier, and it gets hard to predict once other measures start removing filters. One key column is enough.
• Using the measure outside CALCULATE. FILTER(…) alone is a table, not a number. It has to sit inside CALCULATE (or a function such as COUNTROWS).
• Testing lines instead of orders. Read the ticket: “orders over $500” is about totals.