Chapter 5 · 9 levels

CALCULATE and filter context

What filter context a matrix cell and a slicer apply, how CALCULATE adds, replaces and removes filters (FILTER, ALL, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS), shares of a total, and the difference between row context and filter context.

Start

Every number in a Power BI report is a measure evaluated under some set of filters: the row of the matrix, the slicers on the page, the filters on the visual. That set is the filter context, and CALCULATE is the one function that lets a measure change it on purpose.

This chapter is where DAX stops being SUM with extra steps. You read the filter context a cell applies, then use CALCULATE to add filters (completed orders only, web and app only, business customers only), to filter by a measure with FILTER, to remove filters with ALL, ALLEXCEPT and REMOVEFILTERS so a share of total has something to divide by, and to intersect instead of replace with KEEPFILTERS. Along the way you fix the classic mix-up between row context and filter context, the cause of most “same number on every row” bugs.

As in the rest of the track, the matrix and the DAX engine are a practice tool built for this site, and each lesson gives the exact place to do the same in Power BI Desktop. The chapter ends with a boss level: a three-measure review of business customers by category.

Levels

  1. What does this cell filter?

    from Megan Collins, Head of Sales

    filter context, matrix, slicer, relationship, measure

    Quiz

  2. Completed sales with CALCULATE

    from Laura Bennett, Finance Manager

    calculate, filter argument, boolean filter, filter context, measure

    Code

  3. Several filters at once, and IN

    from Kevin Park, B2B Account Lead

    calculate, in operator, multiple filters, and logic, relationships

    Code

  4. Filter by a measure with FILTER

    from Jessica Moore, Marketing Analyst

    filter, values, calculate, context transition, table filter

    Code

  5. Share of total with ALL

    from Rachel Kim, Category Manager

    all, calculate, divide, percent of total, remove filters

    Code

  6. Share within the category with ALLEXCEPT

    from Brian Foster, Lighting Buyer

    allexcept, removefilters, percent of parent, calculate, divide

    Code

  7. Fix it: every product is high demand

    from Daniel Brooks, Inventory Planner

    row context, filter context, context transition, calculated column, calculate

    Code

  8. Keep the row's filter with KEEPFILTERS

    from Olivia Grant, E-commerce Manager

    keepfilters, calculate, filter overwrite, values, filter context

    Code

  9. Boss: the business customer review

    from Megan Collins, Head of Sales

    calculate, divide, removefilters, percent of total, filter context

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.