Ravindra BagaleCourses & study guides Track your progress

Chapter 45

45.1 CALCULATE and Filter Context Mastery

By Ravindra Bagale · Power BI deep series

Chala mitrano — CALCULATE is not magic spice. It changes filter context. Today we see why, with small INR examples from MH sales.

Fictional sample data for learning. All company names and brands belong to their owners. We use the names only so students can understand. Numbers on this page are made up.
After this lesson you can
  • Contrast filter context and row context in plain English.
  • Explain what CALCULATE does to filters.
  • Use REMOVEFILTERS/ALL patterns carefully.
  • Apply KEEPFILTERS when overwrite is wrong.
  • Describe context transition with an iterator example.
  • Spot interview myths about CALCULATE.

Start with a problem (fictional). Intern Kabir Mehta wrote Sales YoY with CALCULATE but every slicer died. Mentor Priya showed he blanketed the model with ALL(everything). In Nashik vs Pune (fictional), they rebuild: clear only Date when needed, KEEPFILTERS on Region, and respect row context inside SUMX.

Sample layout — fictional data: filter context, row context, CALCULATE, transition
Sample layout — fictional data (not a real Service screenshot)

1. Filter context vs row context

Why: Students mix what the visual filtered with which row we are on.

What: Filter context comes from visuals, slicers, and CALCULATE. Row context exists when a formula iterates rows (calculated column, FILTER, SUMX).

How — name them out loud:

Vertical steps (one under another)

  1. Look at a card with City slicer = Nashik — that is filter context.
  2. Look at a calculated column on Sales — that is row context per row.
  3. Measures usually start with filter context from the visual.
  4. Iterators add row context on top.
  5. Write one sentence for each before coding.

Tiny example: Card shows INR 12.4L for Nashik because filter context keeps City = Nashik (fictional).

Ravindra Bagale's Tip

Friends, if you cannot say which context you are in, do not nest CALCULATE yet.

2. What CALCULATE actually does

Why: People say CALCULATE makes it a measure — incomplete.

What: CALCULATE evaluates an expression under a modified filter context (and can force context transition).

How — modify on purpose:

Vertical steps (one under another)

  1. Start from the visual filters.
  2. Add/replace/remove filters in CALCULATE arguments.
  3. Evaluate the expression in that new filter context.
  4. Return the scalar result to the visual.
  5. Test with one city and one month only.

Tiny example: Sales LY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])) keeps City slicer, shifts dates (concept).

Ravindra Bagale's Tip

Friends, CALCULATE is a filter wrench — not a performance fairy.

3. REMOVEFILTERS / ALL — clear with care

Why: ALL(everything) makes percent of total wrong or ignores the user slicer when you did not mean to.

What: REMOVEFILTERS (or ALL on a table/column) clears filters so you can compute a denominator or ignore a dimension.

How — clear only what you must:

Vertical steps (one under another)

  1. For percent of all cities: clear City filters in the denominator, keep Date.
  2. Prefer REMOVEFILTERS('City'[City]) over removing the whole model.
  3. Verify the slicer for Date still affects both parts.
  4. Name measures percent of Total City clearly.
  5. Compare card with and without City slicer.

Tiny example: Percent of MH = DIVIDE([Sales], CALCULATE([Sales], REMOVEFILTERS('City'[City]))) — Pune share of all cities (fictional).

Ravindra Bagale's Tip

Friends, clearing Date by accident is the classic YoY bug — be surgical.

4. KEEPFILTERS when overwrite hurts

Why: CALCULATE filter arguments replace filters on the same column by default — sometimes you wanted to intersect.

What: KEEPFILTERS keeps existing filters and intersects with the new predicate.

How — intersect instead of replace:

Vertical steps (one under another)

  1. Notice when a slicer is ignored after CALCULATE.
  2. Wrap the filter argument with KEEPFILTERS(...).
  3. Retest with two slicers on related columns.
  4. Document why KEEPFILTERS is there.
  5. Prefer clear model design over clever nesting.

Tiny example: Region = West with KEEPFILTERS still respects a City slicer inside West (concept).

Ravindra Bagale's Tip

Friends, if the slicer died, ask: replace vs intersect — KEEPFILTERS is often the fix.

5. Context transition (simple picture)

Why: Inside SUMX, you are in row context; a naked measure may not see the row as a filter.

What: Context transition turns row context into filter context — CALCULATE triggers it when you call a measure inside an iterator.

How — see it once:

Vertical steps (one under another)

  1. Write a SUMX over cities of a sales measure.
  2. Notice CALCULATE/measure call applies each city as a filter.
  3. Avoid accidental transition that explodes cardinality.
  4. Prefer clear measures over hidden transitions in columns.
  5. Test on a tiny city table first.

Tiny example: SUMX(City, [Sales]) uses transition so each city filters Sales (mental model).

Ravindra Bagale's Tip

Friends, transition is powerful and expensive on big tables — know when it fires.

6. Interview myths to kill

Why: Myths waste interview minutes.

What: CALCULATE is not always faster, not only for time intel, and not a substitute for a proper Date table.

How — answer cleanly:

Vertical steps (one under another)

  1. Say: modifies filter context.
  2. Give one REMOVEFILTERS denominator example.
  3. Mention KEEPFILTERS for intersect.
  4. Admit when you escalate complex transition bugs.
  5. Refuse ALL(everything) as a default habit.

Tiny example: Strong line: I clear only the columns I must; I keep user slicers unless the measure says otherwise.

Ravindra Bagale's Tip

Friends, draw filter context with boxes on paper in interviews — it looks senior.

Quiz (5 questions)

1. Filter context mainly comes from:

2. CALCULATE's job is to:

3. REMOVEFILTERS on City in a denominator often helps:

4. KEEPFILTERS is useful when:

5. Context transition happens when:

Practice tasks (12) — hints only, no full answers

  1. Task. Define filter context vs row context in two lines.
    Hint: section 1.
  2. Task. Rewrite a wrong CALCULATE makes measures interview myth.
    Hint: section 2.
  3. Task. Write a percent of all cities measure idea with REMOVEFILTERS.
    Hint: section 3.
  4. Task. When would ALL('Date') break a YoY card?
    Hint: clears dates.
  5. Task. Explain KEEPFILTERS with a Region + City slicer story.
    Hint: section 4.
  6. Task. Tiny example: why SUMX(City,[Sales]) needs transition thinking.
    Hint: section 5.
  7. Task. List three CALCULATE interview myths to avoid.
    Hint: section 6.
  8. Task. Nashik INR 12.4L card — which context?
    Hint: filter.
  9. Task. Surgical clear: City only vs whole model — which and why?
    Hint: section 3.
  10. Task. Draw boxes: visual filters to CALCULATE to result.
    Hint: paper.
  11. Task. Name one performance risk of heavy transition on big tables.
    Hint: section 5 tip.
  12. Task. Fix Kabir ALL everything YoY — what do you clear instead?
    Hint: story.

Ravi Sir's cards (Excel card style)

Standalone cards — no tip or photo sections. QR points to this lesson. Download PNG to revise offline.

Interview Q&A: What does CALCULATE really do?
Download interview-card PNG
Comparison Card: Filter context vs row context
Download comparison-card PNG
Revision Card: CALCULATE checklist before you write it
Download revision-card PNG

Think like a professional

In a real tenant, rights and sharing are controlled by Fabric / Power BI admins. Always follow your company policy. This lesson uses fictional people and numbers so you can practise the ideas safely.