DAX CALCULATE and Filter Context: 8 Practice Problems

CALCULATE evaluates an expression in a modified filter context. Its filter arguments can add a new filter, replace an existing filter on the same column, remove filters, or activate a relationship. The difficult part is not remembering the syntax; it is predicting which rows remain visible after those changes.

Use these eight problems to practice that prediction. Assume a star schema where Product, Customer, and 'Date' filter a Sales fact table.

Total Sales =
SUM ( Sales[Sales Amount] )

Before running each solution, write down the filter context you expect. Then test a detail row and the grand total.

Problem 1: Replace the current color

Return sales for blue products even when the visual currently shows another color.

Blue Sales =
CALCULATE (
    [Total Sales],
    Product[Color] = "Blue"
)

A Boolean filter argument replaces an existing filter on Product[Color]. Other Product filters, such as Category, remain active. On a Red row, this measure still evaluates Blue products in the current Category.

Problem 2: Keep the current color and require blue

Now return Blue sales only when Blue is already allowed by the current context.

Blue Sales, Keep Context =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Product[Color] = "Blue" )
)

KEEPFILTERS changes replacement into intersection. A Red row intersected with Blue contains no products, so the result is blank. The Microsoft CALCULATE documentation explains the default overwrite behavior; KEEPFILTERS changes that behavior for the wrapped filter.

Problem 3: Remove only the color filter

Return sales for every color while preserving Product Category, Date, and Customer filters.

Sales, All Colors =
CALCULATE (
    [Total Sales],
    REMOVEFILTERS ( Product[Color] )
)

The scope matters. REMOVEFILTERS(Product) would remove every Product-table filter, including Category. Naming the column is safer when the business rule only asks you to ignore Color.

Problem 4: Calculate share within category

Divide the current product’s sales by sales for all products in its category.

Product Share Within Category =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( Product[Product Name] )
    )
)

The denominator removes the product-name filter but keeps Category. Test this in a matrix with Category above Product Name. At a Category subtotal, no product-name filter exists, so the result should be 100%.

If you remove all filters from Product, the denominator becomes sales across every category. That is a valid formula for a different business question.

Problem 5: Use the shipping-date relationship

Assume Sales[Order Date] has the active relationship to 'Date'[Date], while Sales[Ship Date] has an inactive relationship. Return sales by shipping date.

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[Ship Date], 'Date'[Date] )
)

USERELATIONSHIP enables the named relationship for this calculation. A Date slicer now reaches Sales through Ship Date. Test an order that was placed in one month and shipped in the next; otherwise both measures may coincidentally return the same number.

Problem 6: Apply a table filter at the intended grain

Return sales from products whose visible sales are at least 10,000.

Sales From Large Products =
SUMX (
    FILTER (
        VALUES ( Product[ProductKey] ),
        [Total Sales] >= 10000
    ),
    [Total Sales]
)

The grain is Product, not Category or Sales row. VALUES(Product[ProductKey]) creates the product set visible in the current context. The measure reference inside FILTER is evaluated for each product through context transition.

Problem 7: Preserve a report filter while adding a range

Return sales from dates on or after January 1, 2026, without expanding a report that is already filtered to March.

Sales From 2026 =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( 'Date'[Date] >= DATE ( 2026, 1, 1 ) )
)

The March report context intersects the range and remains March. Without careful context reasoning, developers sometimes remove Date filters first and accidentally return the whole year.

Problem 8: Explain the total before changing it

Consider this measure:

Profitable Product Count =
COUNTROWS (
    FILTER (
        VALUES ( Product[ProductKey] ),
        [Margin] > 0
    )
)

At each Category row it counts profitable products in that category. At the grand total it reevaluates over all visible products; it does not add the displayed row counts. That recalculation is usually correct.

If the total surprises you, inspect the intended grain before forcing a sum of visible rows. Our guide to why DAX grand totals look wrong walks through that decision.

CALCULATE verification checklist

Before accepting a measure, answer:

• Which filters enter the cell?
• Which filter arguments add, replace, intersect, or remove filters?
• Which relationships carry those filters to the fact table?
• At what grain does each iterator run?
• What changes at a subtotal or grand total?
• Can you calculate one small case by hand?

The reliable mental model is: start with the current filter context, apply each CALCULATE modifier, then evaluate the expression. Practice that loop in the DAX Solver CALCULATE exercises.