FILTER, ALL, ALLEXCEPT & REMOVEFILTERS

Power BI · DAX (Data Analysis Expressions)

Theory

These functions control which rows a calculation sees. FILTER(table, condition) is an iterator that returns the rows of a table that meet a condition; inside CALCULATE it lets you filter on expressions or measures, for example FILTER(Customers, [Total Sales] > 1000). ALL(table or columns) returns all rows ignoring filters, and as a CALCULATE argument it removes filters. REMOVEFILTERS does the same removal and reads more clearly, but can only be used as a CALCULATE modifier. ALLEXCEPT(table, columns) removes all filters on a table except those on the listed columns.

The classic use is percentage of total: DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Region]))). For North the numerator is ₹3,200 and the denominator ignores the Region filter, ₹6,200, so the share is 51.6%; West is 38.7% and South 9.7%. ALLSELECTED is a related function that respects the user's outer slicer selections.

Why we use it

Ratios, shares and benchmarks need a denominator that ignores some filters. FILTER handles conditions that a simple column filter cannot express.

When to use it

Use REMOVEFILTERS or ALL for grand-total denominators, ALLEXCEPT to keep one grouping (such as Category) while ignoring the rest, and FILTER only when the condition needs a measure or complex logic. Filtering a whole large table with FILTER is slow; filter a column instead.

How it works

  1. Need a denominator or special filter
  2. Should some filters be ignored? Yes: Use ALL / REMOVEFILTERS / ALLEXCEPT in CALCULATE. No: Keep the visual's filters.
  3. Does the condition need a measure or complex logic? Yes: Wrap it in FILTER over a small table. No: Use a simple column predicate.
  4. Divide or compare with the normal measure
  5. Shares and benchmarks per cell

Syntax

FILTER(<table>, <filter>)
ALL([<table> | <column>[, <column>[, …]]])
ALLEXCEPT(<table>, <column>[, <column>[, …]])
REMOVEFILTERS([<table> | <column>[, <column>[, …]]])
ALLSELECTED([<tableName> | <columnName>[, …]])

Example

% of Total Sales = 
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], REMOVEFILTERS(Sales[Region]))
)

// Share within the product's own category
% of Category = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Products, Products[Category])))

// Sales from customers with more than ₹1,000 of sales
Big Customer Sales = CALCULATE([Total Sales], FILTER(Customers, [Total Sales] > 1000))  // 2,700 + 2,400 = 5,100

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic