KEEPFILTERS in CALCULATE — Intersect Instead of Replace
KEEPFILTERS tells CALCULATE to intersect a new filter with filters that already exist on that column, instead of replacing them — the difference between “Online only, ignore City” and “Online inside the City I sliced”.
Friends! CALCULATE filter arguments look innocent until a slicer “disappears”. Why? Because many filters replace the filter on that column. How? We compare a plain boolean filter with KEEPFILTERS on a fictional FreshBasket Sales model (City + Channel). Slow and clear — this saves interview marks.
मित्रांनो! CALCULATE filter arguments look innocent until a slicer “disappears”. Why? Because many filters replace the filter on that column. How? We compare a plain boolean filter with KEEPFILTERS on a fictional FreshBasket Sales model (City + Channel). Slow and clear — this saves interview marks.
मित्रों! CALCULATE filter arguments look innocent until a slicer “disappears”. Why? Because many filters replace the filter on that column. How? We compare a plain boolean filter with KEEPFILTERS on a fictional FreshBasket Sales model (City + Channel). Slow and clear — this saves interview marks.
Quick answer
Vertical intent check:
- Ask: replace the column filter, or intersect with it?
- Plain:
CALCULATE ( [Total Sales], Sales[Channel] = "Online" )often replaces Channel filters. - Intersect: wrap with
KEEPFILTERS ( Sales[Channel] = "Online" ). - Compare two cards while a City slicer is active.
- Name the measure so the intent is obvious later.
- Not every CALCULATE needs KEEPFILTERS — only when intersection is the rule.
CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Channel] = "Online" ) )
Why KEEPFILTERS, rows in / number out
| Order ID | City | Amount | Channel |
|---|---|---|---|
| A | Mumbai | 100 | Online |
| B | Mumbai | 40 | Store |
| C | Nashik | 80 | Online |
- City slicer = Mumbai. Rows in: A + B. Base
[Total Sales]= 140. - You want Online sales inside Mumbai.
- With a careful intersect mindset, keep City and add Channel = Online. Rows kept: only A. Number out: 100.
- If a replace-style Channel filter wiped a Channel slicer you cared about, the card can lie.
KEEPFILTERS ( Sales[Channel] = "Online" )is the “and also Online” tool when you need intersection with existing filters on that column.
Why you care:
- Managers say “Online for the cities on the slicer,” not “Online for the whole country while the City slicer lies.”
- Interviews ask replace vs intersect.
Boolean filters inside CALCULATE often replace an existing column filter; wrap with KEEPFILTERS when you want to intersect instead.
मित्रांनो — Boolean filters inside CALCULATE often replace an existing column filter; wrap with KEEPFILTERS when you want to intersect instead.
मित्रों — Boolean filters inside CALCULATE often replace an existing column filter; wrap with KEEPFILTERS when you want to intersect instead.
What do I need before this guide?
- CALCULATE / filter context.
- FILTER inside CALCULATE (optional warm-up).
- Course: CALCULATE.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
The surprise: replace vs intersect
- You slice City = Mumbai.
- You write a CALCULATE that sets Channel = Online.
- Depending on how filters combine, you may no longer be “Online inside Mumbai” the way you imagined.
- Interviewers love asking whether you wanted replace or intersect.
Plain CALCULATE filter (replace mindset)
Online Sales Replace =
CALCULATE (
[Total Sales],
Sales[Channel] = "Online"
)
- Useful when you truly want Online sales regardless of other Channel filters.
- Dangerous when you thought City + Online would both stay the way you pictured.
- Test with City and Channel slicers before you trust the card.
KEEPFILTERS (intersect mindset)
Story: City slicer stays on; KEEPFILTERS ( Sales[Channel] = "Online" ) keeps only Online rows that still match the slicer.
मित्रांनो — Story: City slicer stays on; KEEPFILTERS ( Sales[Channel] = "Online" ) keeps only Online rows that still match the slicer.
मित्रों — Story: City slicer stays on; KEEPFILTERS ( Sales[Channel] = "Online" ) keeps only Online rows that still match the slicer.
Online Sales Kept =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Sales[Channel] = "Online" )
)
Vertical reading:
- Start from current filter context (City slicer, visuals, …).
- KEEPFILTERS adds Channel = Online without wiping existing filters the way a naked replace would.
- Result = rows that satisfy both stories — Mumbai Online 100 on the sample.
Pattern: CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Channel] = "Online" ) ) — filter argument wrapped on purpose.
मित्रांनो — Pattern: CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Channel] = "Online" ) ) — filter argument wrapped on purpose.
मित्रों — Pattern: CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Channel] = "Online" ) ) — filter argument wrapped on purpose.
FreshBasket story (fictional)
FreshBasket ops want “Online sales for the cities on the slicer”, not “Online for the whole country while the City slicer lies to us”. That sentence is KEEPFILTERS.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Slicer ignored | Filter replaced | Wrap with KEEPFILTERS |
| Still wrong City | Wrong column / model | Check Channel and City columns |
| Same as base measure | Condition always true | Verify sample data |
| Overused KEEPFILTERS | Copy-paste habit | Use only when intersection is required |
Ghabru naka 😅 — one side-by-side card test beats ten forum threads.
Ravindra Bagale's Tip
Interview trap: “Does CALCULATE add or replace filters?” Calm answer: “Filter arguments typically replace filters on that column; I use KEEPFILTERS when I need to intersect with the existing filter context.” Got it?
Ravindra Bagale's Tip – मराठी
Interview trap: “CALCULATE filters add करतो की replace?” शांत उत्तर: “Filter arguments त्या column वरती filters replace करतात; जेव्हा existing filter context शी intersect करायचे असते तेव्हा मी KEEPFILTERS वापरतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview trap: “CALCULATE filters add करता है या replace?” शांत जवाब: “Filter arguments उस column पर filters replace करते हैं; जब existing filter context से intersect करना हो तब मैं KEEPFILTERS इस्तेमाल करता हूँ.” समझ में आया?
Practice task
- Build Online Sales Replace and Online Sales Kept.
- Slice City = Mumbai, Channel = all (expect Online-in-Mumbai 100 with kept intent).
- Write which measure kept City.
- Flip Channel slicer; note differences.
- Rename measures so replace vs kept is obvious.
Got it? CALCULATE filters often replace; KEEPFILTERS intersects. Test with a City slicer before you swear the measure is right. Next: IF and SWITCH. Let's continue.
समजलं का? CALCULATE filters often replace; KEEPFILTERS intersects. Test with a City slicer before you swear the measure is right. Next: IF and SWITCH. Aata pudhe jaauya.
समझ में आया? CALCULATE filters often replace; KEEPFILTERS intersects. Test with a City slicer before you swear the measure is right. Next: IF and SWITCH. आगे बढ़ते हैं.
Frequently asked questions
What does KEEPFILTERS do?
It tells CALCULATE to intersect the new filter with existing filters on that column, instead of replacing them.
When do I need it?
When a CALCULATE filter argument would wipe a slicer/visual filter you still want on the same column (or related filter), and you need both to apply.
Is KEEPFILTERS only for booleans?
You wrap filter arguments (including more advanced ones) — beginners usually meet it on simple column filters first.
Does every CALCULATE need KEEPFILTERS?
No. Many measures should replace filters on purpose. Use it when intersection is the business rule.
Related to FILTER?
Different jobs: FILTER builds a table; KEEPFILTERS changes how a filter argument combines with existing context.
Course lesson?
CALCULATE — the most important function, in the DAX chapter.