One measure, a new answer in every cell.

Lesson 4 of 6About 9 minutes5 steps

In Power BI, a measure has no single value: it’s worked out again for every cell, under whatever filters apply there. Understand that, and DAX stops being mysterious.

  • Explain filter context
  • Say what CALCULATE and ALL do
  • Tell a measure from a calculated column

You need: Lesson 3.

  1. 1Statistics you use every day
  2. 2SQL that answers questions
  3. 3Charts that answer the question
  4. 4Power BI’s filter context
  5. 5A/B tests
  6. 6Guesstimates
0 of 5 steps

  1. One measure, many answers

    Predict first

    Total Sales = SUM(Sales[Amount]). In a matrix of regions by category, what does each cell show?

    Just show me

    The sum for its own region and category: each cell filters the table first, then the measure runs.

    A visual’s rows, columns and slicers each add filters; together they’re the cell’s filter context, and the measure runs inside it. The table below is a small made-up example: change the slicer or the measure and every cell is worked out again, and tap a cell to see its filters.

    Slicer: channel

    The measure

    Total Sales = SUM(Sales[Amount])

    RegionMobilesFashionGroceryTotal
    North
    South
    East
    West
    Total

    Made-up sales in ₹ lakh: a table of 24 rows, each a region, a category, a channel and an amount

    Quick check

    What decides a cell’s filter context?

    Everything that narrows the data for that cell.

  2. CALCULATE changes the filters

    Predict first

    Share of regions = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Region]))). What does ALL do here?

    Just show me

    It removes the region filter for that part, so each cell is divided by its category’s total across every region.

    CALCULATE runs an expression in a changed filter context: it adds a filter, replaces one, or removes one with ALL. It’s the most important function in DAX, and the answer to “% of total”, “the same period last year” and “sales for one region whatever the visual shows”.

    Quick check

    Sales for the North region only, whatever the visual shows?

    CALCULATE with a filter argument replaces the region filter.

  3. Row context is different

    Predict first

    A calculated column, Amount × 1.18: when is it worked out?

    Just show me

    Row by row, at each refresh, and stored in the model: it has a row context, not a filter context.

    A calculated column sees one row at a time (row context); a measure sees the filtered table (filter context). Use a column when you need to slice by the value, such as a price band, and a measure for anything you add up. Columns take memory; measures are worked out when asked.

    Quick check

    You need profit margin in a card and a chart. Column or measure?

    A ratio of sums must be a measure, or the rows’ margins get added up wrongly.

  4. Why totals look wrong

    Predict first

    A matrix’s total row differs from adding up the cells. Is Power BI broken?

    Just show me

    No: the total cell runs the measure with fewer filters, which is right for sums and surprising for averages, ratios and distinct counts.

    An average’s total is the average of everything, not the average of the averages; a distinct count’s total counts each customer once even if they appear in several rows. Knowing this, and when to reach for SUMX or another measure, is a favourite Power BI interview question.

    Quick check

    Distinct customers by city: why might the cities add up to more than the total?

    Each city counts them; the total counts them once.

  5. Model first, DAX second

    Predict first

    Your DAX keeps getting more complicated. The usual fix?

    Just show me

    Usually the model: with a star schema, filters flow the way you expect.

    Filters flow from dimension tables (dates, products, regions) to the fact table (sales) along relationships. With a clean star schema, most measures stay simple; with a tangle of tables, every measure needs a workaround.

    Quick check

    In a star schema, what’s in the fact table?

    Facts are the measurable events; dimensions describe them.

In the interview: Power BI’s filter context.

Ask Prism about this lesson

Sources

  1. Microsoft Learn, “DAX overview”, context, measures and calculated columns.
  2. Microsoft Learn, “CALCULATE function (DAX)”.

Back: lesson 3, Charts that answer the question