Understanding DAX Filter Functions in Simple Words

[ajaybabuinturi]

When we working with Power BI, we often want to control what data we see. Think of it like looking at the world through different glasses. Some glasses show everything, some show only specific things, and some block everything else except one thing.

That’s exactly what DAX filter functions do, they decide what data should be visible for your calculation.

In this blog, I will walk you through the 5 most important filter functions in DAX

• FILTER
• ALL
• ALLEXCEPT
• ALLSELECTED
• REMOVEFILTERS

We will go step by step, with examples, real world comparisons, and easy rules on when to use each.

FILTER: The Custom Selector

What it does: FILTER allows you to select precisely which rows to retain.

Example: Suppose you have a jar containing candies of various colours. If you say, “I only want red candies,” then FILTER will only keep the red ones for you.

DAX Example: FILTER (Table_Jar, Table_Jar[CandyColour] = “Red”) --> This means: From the Table_Jar table, only keep candies where candy colour = Red.

When to use:

• When you want very specific conditions.
• Often used inside CALCULATE.

ALL: Show Me Everything

What it does:  ALL removes filters, like wiping the slate clean

Example: Imagine you’re playing hide and seek. If you close your eyes and say “I don’t care where anyone is hiding, show me everyone”, that’s ALL.

DAX Example: CALCULATE(SUM(Sales[Amount]), ALL(Sales)) --> This ignores all filters and displays the total sales regardless of what filter is used in the report.

When to use:

• When you need grand totals.
• Useful in % calculations.

ALLEXCEPT: Keep Only What Matters

What it does:  Removes all filters except the ones you keep it.

Example: You are in a candy shop. You say, “Don’t care about candy colour or size, but keep the brand filter.” That’s ALLEXCEPT.

DAX Example: CALCULATE(SUM(Table_Jar[SalesAmount]), ALLEXCEPT(Table_Jar, Table_Jar[Brand])) --> This keeps only the Product filter, but ignores everything else.

When to use:

• When you want calculations by groups.

ALLSELECTED: Respect My Choices

What it does:  ALLSELECTED respects the user’s selection in visuals but removes filters within.

Example: You are in a candy shop. You say to the shopkeeper, "I don't want to see anything except chocolates and lollipops today. Now, of those two, show me the totals."

DAX Example: CALCULATE(SUM(Table_Jar[SalesAmount]), ALLSELECTED(Table_Jar)) --> Use ALLSELECTED when you need to calculate based on what people selected in slicers or visuals.

When to use:

• When you need totals based only on what users have selected.

REMOVEFILTERS: The Gentle Cleaner

What it does:  REMOVEFILTERS remove/clear filters from specific columns or tables.

Example: You have a drawing book. Someone put stickers on certain pages. REMOVEFILTERS means peeling off those stickers so you can see the full picture.

DAX Example: CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Region])) --> This clears the Region filter but keeps others.

When to use:

• When you want to ignore only certain filters without affecting the rest.
• Similar to ALL, but more targeted.

Quick Comparison

Function	What It Does	Example Use
FILTER	Keeps only rows that meet a condition	Table_Jar[CandyColour] = “Red”
ALL	Removes all filters	Grand Total
ALLEXCEPT	Removes all filters except chosen ones	Total by Brand
ALLSELECTED	Removes filters but respects user selections	% of Selected Total
REMOVEFILTERS	Clears filters from specific columns/tables	Ignore only Region filter

Simple Rules for Choosing

• Use FILTER when you need specific conditions.
• Use ALL when you need totals without filters.
• Use ALLEXCEPT when you need totals by groups.
• Use ALLSELECTED when you need user-controlled totals.
• Use REMOVEFILTERS when you need to ignore only certain filters.

DAX filter functions may sound complex at first, but they are just like rules for how you want to look at your data.

If you remember the candy shop examples, you will never forget them:

• FILTER = Pick only red candies
• ALL = Show all candies
• ALLEXCEPT = Keep brand, ignore everything else
• ALLSELECTED = Respect my candy selection
• REMOVEFILTERS = Remove only a few candy rules

[AbdulBasha]

Good information 👍 

[ketanTiwariAa]

If anyone have to understand Filter functions in DAX in more precise and with clearity, please go through it. Explained in best possible way. Thanks Ajay it really helped me clearing my concept on filters.

[Nabha-Ahmed]

insightful,,thank you for sharing

[Magudeswaran_MR]

@ajaybabuinturi 
Thank you for sharing the DAX filter functions along with their real-time data analysis use cases. Your insights are incredibly helpful and much appreciated!

[Enzo_Rideau]

Hey @ajaybabuinturi  ! Very nice content. 
I will share it on LinkedIn (and translate it in French). 

and mention you as a source 🙂