Ravindra BagaleCourses & study guides Track your progress

Guides

ALL vs ALLEXCEPT vs ALLSELECTED vs REMOVEFILTERS in DAX

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS reshape filter context inside CALCULATE — clear filters, keep some columns, respect outer visual selections, or remove filters with clear intent — the toolkit behind “% of total” measures.

Chala mitrano! After CALCULATE, the next interview cluster is the ALL family. Why? Percent of total is everywhere in business reports. How? We meet REMOVEFILTERS first (clearest), then ALL, ALLEXCEPT and ALLSELECTED with a FreshBasket (fictional) City example. One toolkit, four tools.

Quick answer

Vertical toolkit:

  1. Start from a solid [Total Sales].
  2. REMOVEFILTERS ( column/table ) — clear those filters inside CALCULATE (modern, readable).
  3. ALL — classic “remove filters / return all values” building block.
  4. ALLEXCEPT — clear filters on a table except listed columns.
  5. ALLSELECTED — respect outer/visual selections when computing a partial total.
  6. Build one % of all cities measure with DIVIDE.
Pct = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( … ) ) )

Why clear filters, rows in / number out

City Amount
Mumbai 100
Mumbai 40
Nashik 80
  1. Matrix row = Mumbai. Rows in for [Total Sales]: Mumbai only. Number out: 140.
  2. Denominator for “% of all cities” must ignore the City on the row.
  3. CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer[City] ) ) clears City. Rows in: all three. Number out: 220.
  4. DIVIDE ( 140, 220 ) ≈ 63.6%. That is Mumbai’s share of all cities.

Why each tool:

  1. REMOVEFILTERS — “drop this filter; keep the rest.” Clearest for beginners.
  2. ALL — classic sibling; can also return a table of all values.
  3. ALLEXCEPT — “clear the Customer table filters except Region.”
  4. ALLSELECTED — “percent of what this visual currently shows,” not always the whole universe.
ALL family ALL ALLEXCEPT ALLSELECTED REMOVEFILTERS ALL ALLEXCEPT ALLSELECTED REMOVEFILTERS toolkit

ALL / ALLEXCEPT / ALLSELECTED / REMOVEFILTERS clear or reshape filter context — the toolkit behind “% of total” patterns.

What do I need before this guide?

Before and after (look at the tables first)

Before REMOVEFILTERS Mumbai rows only; total 140.

Before

After REMOVEFILTERS All cities visible for denominator; total 220.

After

REMOVEFILTERS (learn this early)

Sales All Cities =
CALCULATE (
    [Total Sales],
    REMOVEFILTERS ( Customer[City] )
)

Adjust table/column names to your model. Meaning: evaluate Total Sales as if City were not filtered.

ALL (classic sibling)

ALL vs ALLEXCEPT Clear all vs keep listed columns ALL → drop filters ALLEXCEPTkeep listed columns pair

ALL ( table ) drops filters on that table; ALLEXCEPT keeps listed columns filtered and clears the rest on that table.

  1. ALL ( Customer[City] ) / ALL ( Customer ) patterns clear filters and can return tables of values.
  2. Many older examples use ALL inside CALCULATE for the same “ignore filter” idea.
  3. REMOVEFILTERS is often clearer when you only want to drop filters.

ALLEXCEPT

-- sketch: keep Region filters, clear other filters on the table
CALCULATE (
    [Total Sales],
    ALLEXCEPT ( Customer, Customer[Region] )
)

Use when you must preserve one (or more) column filters while clearing the rest on that table.

ALLSELECTED

ALLSELECTED REMOVEFILTERS Outer selections vs clear filters ALLSELECTEDrespect outer visual REMOVEFILTERSclear listed filters pair

ALLSELECTED respects outer visual selections; REMOVEFILTERS is the clearer modern way to drop filters on columns/tables.

  1. Useful for “percent of what the visual currently shows”.
  2. Respects outer selections more carefully than a blunt ALL on everything.
  3. Practise after you can write one REMOVEFILTERS denominator confidently.

Tiny % of total pattern

Pct of All Cities =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer[City] ) )
)
  1. Numerator respects the City on the row/slicer (140 for Mumbai).
  2. Denominator clears City (220).
  3. Format as percentage.

Shop analogy: Mumbai’s share of the day’s till. Numerator = Mumbai drawer. Denominator = whole shop drawer.

Mistakes and calm fixes

Symptom Likely cause Fix
Always 100% Cleared nothing / wrong column Remove the filter that the visual actually uses
Always blank DIVIDE by zero / no rows Protect with DIVIDE; check filters
Wrong % in matrix Needed ALLSELECTED vs ALL Compare both on a sample page
Too many ALL layers Copy-paste stack Rewrite one clear CALCULATE

Ghabru naka 😅 — one working % measure beats five half-understood ALL calls.

Ravindra Bagale's Tip

Interview trap: “ALL vs REMOVEFILTERS?” Calm answer: “Both can clear filters; I prefer REMOVEFILTERS when I only need to drop filters inside CALCULATE — it reads cleaner.” Got it?

Practice task

  1. Build Sales All Cities with REMOVEFILTERS (expect 220 on the sample).
  2. Build Pct of All Cities with DIVIDE.
  3. Put City on a matrix + the % measure (Mumbai ≈ 64%).
  4. Write which filters still applied to the denominator.
  5. Skim ALLSELECTED examples in the course without rushing.

Learn it properly

Course lessons:

Related guides: FILTER inside CALCULATE · RELATED

Got it? REMOVEFILTERS/ALL clear filters; ALLEXCEPT keeps listed columns; ALLSELECTED respects outer visuals. One % measure is enough practice. Next: RELATED. Let's continue.

Frequently asked questions

What does ALL do?

Removes filters from a table or column (and returns all row values of that table/column in many uses) — classic “ignore this filter” building block.

What does REMOVEFILTERS do?

A clearer function to remove filters on tables/columns inside CALCULATE without implying you need the ALL table result.

What does ALLEXCEPT do?

Removes filters from a table except for the columns you list — keep Region, clear other Sales filters, for example.

What does ALLSELECTED do?

Returns values as filtered by outer/visual selections — often used for percent of what the user currently sees.

Which should beginners learn first?

REMOVEFILTERS (or ALL on one column) inside CALCULATE for a simple “% of all cities” card.

Course lesson?

ALL, ALLEXCEPT, REMOVEFILTERS, ALLSELECTED in the DAX chapter.