Power BI DAX Filter: The Complete Guide to FILTER, CALCULATE, Multiple Conditions and KEEPFILTERS

A Power BI DAX filter limits which rows a calculation sees. In most measures you do it with CALCULATE plus a simple condition, like CALCULATE([Total Sales], Sales[Region] = "West"). You reach for the FILTER function only when the condition needs a measure, columns from two tables, or row-by-row logic that a plain condition can't express.

This guide is for report builders who can write a SUM measure but get lost when filters stop behaving: totals that ignore the slicer, an OR condition that won't compile, or a measure that crawls. Every example runs on the same 8-row sample table, so you can check your numbers against ours.

In this guide

• What is the Power BI DAX filter function?
• DAX filter context in 2 minutes
• Calculate vs FILTER: which one to use
• Build the sample model (follow along)
• Multiple conditions, OR, lists and text
• How to filter a measure in Power BI
• Power BI DAX filter ALL, REMOVEFILTERS and ALLSELECTED
• KEEPFILTERS: stop your filter overwriting the slicer
• Power BI DAX filter table: calculated tables and queries
• Common errors, performance and best practices
• Wrap-up and next steps
• FAQ

What is the Power BI DAX filter function?

FILTER is a DAX table function. It takes a table, checks a condition on every row, and returns a smaller table with only the rows where the condition is TRUE. Microsoft describes it as a function that "returns a table that represents a subset of another table or expression" (FILTER function, Microsoft Learn).

The key point beginners miss: FILTER returns a table, not a number. You never drop it into a card on its own. You pass its result to a function that wants a table: CALCULATE, SUMX, COUNTROWS, AVERAGEX, or a calculated table.

DAX filter syntax

FILTER ( <table>, <filter> )

• table: the table to scan. It can be a model table (Sales) or any expression that returns a table (VALUES(Sales[Product]), ALL('Date')).
• filter: a TRUE/FALSE expression evaluated for each row, for example Sales[Amount] > 1000.

Here is the smallest useful FILTER measure. It counts orders above 1,000:

Big Orders Count =
COUNTROWS (
    FILTER ( Sales, Sales[Amount] > 1000 )
)

FILTER is an iterator: it walks the table row by row. That is what makes it flexible (it can call measures and compare columns), and also why it can be slow on a fact table with millions of rows. Keep that trade-off in mind for the rest of this guide.

Where you can use FILTER

FILTER works in measures, calculated columns, calculated tables and visual calculations. One limit to know: it is not supported in DirectQuery mode when used in calculated columns or row-level security rules. In measures on a DirectQuery model it works fine.

DAX filter context in 2 minutes

Before you change filters, you need to know what filters already exist. Filter context is the set of filters active when a measure is evaluated. It comes from four places: the row or column of the visual, slicers, the filter pane (visual, page and report filters), and cross-highlighting from other visuals.

When a matrix shows Total Sales for the "East" row, the measure itself is plain SUM(Sales[Amount]). The visual adds the filter Sales[Region] = "East" before the sum runs. That is why one measure gives a different number on every row.

Every DAX filter technique in this guide does one of three things to that context:

1. Add a filter (Tech products only).
2. Replace a filter (always West, whatever the slicer says).
3. Remove a filter (all regions, for a percent of total).

If a measure returns a surprising number, ask which of the three it is doing. Nine times out of ten that answers the question. The site's Power BI tutorials cover row context and context transition in more depth if you want the full theory.

Calculate vs FILTER: which one to use

This is the most searched confusion around DAX filtering. They are not rivals: CALCULATE changes the filter context and then evaluates an expression; FILTER produces a table. You often use them together. The real question is what you put in CALCULATE's filter arguments.

Calculate filter DAX: the three kinds of filter argument

According to the CALCULATE documentation, each filter argument can be one of:

• A Boolean filter expression: Sales[Category] = "Tech". It can reference columns from one table only, can't reference a measure, can't use nested CALCULATE, and can't use functions that scan or return a table (except inside an aggregation).
• A table filter expression: usually FILTER(...), VALUES(...) or ALL(...). Anything goes, because you build the table yourself.
• A filter modifier: REMOVEFILTERS, ALL, ALLEXCEPT, KEEPFILTERS, USERELATIONSHIP, CROSSFILTER.

Behind the scenes, a Boolean filter like Sales[Category] = "Tech" is shorthand for FILTER(ALL(Sales[Category]), Sales[Category] = "Tech"). Notice the ALL: the Boolean filter scans one column, ignoring any existing filter on that column. That single detail explains most "my slicer is ignored" questions.

The same measure written three ways

-- 1. Boolean filter (recommended)
Tech Sales =
CALCULATE ( [Total Sales], Sales[Category] = "Tech" )

-- 2. FILTER over one column (same result, explicit)
Tech Sales v2 =
CALCULATE (
    [Total Sales],
    FILTER ( ALL ( Sales[Category] ), Sales[Category] = "Tech" )
)

-- 3. FILTER over the whole table (works, but slowest)
Tech Sales v3 =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Category] = "Tech" )
)

Versions 1 and 2 behave identically. Version 3 respects existing filters (FILTER runs inside the current context) but scans every row of the fact table and filters every column of it. Microsoft's own guidance, Avoid using FILTER as a filter argument, says to pass Boolean expressions whenever possible because Import tables are column stores optimised for exactly that.

Decision table

You want to	Use	Respects slicer on same column?	Speed
Filter one column by a fixed value	CALCULATE([m], T[Col] = "x")	No (overwrites)	Fastest
Same, but keep the slicer	KEEPFILTERS(T[Col] = "x")	Yes (intersects)	Fastest
Several values in one column	T[Col] IN {"a","b"}	No (wrap in KEEPFILTERS to keep)	Fast
OR across columns of one table	T[A] = "x" || T[B] = "y"	No	Fast
Condition uses a measure	FILTER(VALUES(T[Col]), [Measure] > n)	Yes	Medium
Condition uses columns from two tables	FILTER(Fact, RELATED(...) ...)	Yes	Slowest
Ignore all filters on a column	REMOVEFILTERS(T[Col]) or ALL	No (removes)	Fast
Ignore visual rows, keep slicers	ALLSELECTED(T[Col])	Yes	Fast

My rule of thumb: start at the top of this table and only move down when the compiler (or the result) forces you to.

Build the sample model (follow along)

Every result below comes from this tiny table. Build it once and you can paste each measure and compare. It takes about three minutes.

1. In Power BI Desktop, go to Home > Enter data, name the table Sales and paste the rows below. Set Date to type Date and Amount to Whole number.
2. Go to Modeling > New table and create a date table: Date = CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2026, 12, 31 ) ).
3. In Model view, drag 'Date'[Date] onto Sales[Date] to make a one-to-many relationship.
4. Select the Date table and use Mark as date table, choosing the Date column.
5. Create the base measure: Total Sales = SUM ( Sales[Amount] ).
6. Add a matrix with Sales[Region] on rows so you can watch the filter context change.

OrderID	Date	Region	Product	Category	Amount
1	2026-01-05	West	Laptop	Tech	1200
2	2026-01-09	East	Phone	Tech	800
3	2026-01-15	West	Desk	Furniture	450
4	2026-02-02	North	Laptop	Tech	1300
5	2026-02-11	East	Chair	Furniture	150
6	2026-02-20	West	Phone	Tech	700
7	2026-03-03	South	Desk	Furniture	500
8	2026-03-18	East	Laptop	Tech	1250

Check yourself: Total Sales = 6,350. By region: East 2,200, North 1,300, South 500, West 2,350. By category: Tech 5,250, Furniture 1,100. With the sample in place, Tech Sales from the previous section returns 5,250 and Big Orders Count returns 3.

In a real model, Region, Product and Category would live in dimension tables (a star schema). We keep one flat table here so the filters are easy to see. The patterns are the same; with dimensions you filter the dimension column instead, for example 'Product'[Category] = "Tech".

Multiple conditions, OR, lists and text

This section answers the long-tail searches people type most: AND, OR, lists of values, "not in", text matching and dates. All results use the sample table.

Power BI DAX filter multiple conditions (AND)

Separate filter arguments in CALCULATE are combined with AND. This is the cleanest way to say "Tech and West":

Tech West Sales =
CALCULATE (
    [Total Sales],
    Sales[Category] = "Tech",
    Sales[Region] = "West"
)
-- Result: 1,900 (Laptop 1,200 + Phone 700)

You can also write Sales[Category] = "Tech" && Sales[Region] = "West" in one argument. Same result here, but separate arguments are easier to read and each one can be wrapped in KEEPFILTERS on its own. Inside FILTER, you must use && because FILTER takes one condition:

Tech West Orders =
COUNTROWS (
    FILTER ( Sales, Sales[Category] = "Tech" && Sales[Region] = "West" )
)
-- Result: 2

Power BI DAX filter OR condition

OR is where people get stuck, because comma-separated arguments always mean AND. Use the || operator (or the OR() function, which takes exactly two arguments) inside one filter argument:

Tech or West Sales =
CALCULATE (
    [Total Sales],
    Sales[Category] = "Tech" || Sales[Region] = "West"
)
-- Result: 5,700 (all Tech 5,250 + the West Desk 450)

This works as a Boolean filter because both columns are in the same table. If the columns sit in different tables (say 'Product'[Color] and Customer[Country]), a Boolean filter fails and you need FILTER over the fact table with RELATED:

Red or US Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        RELATED ( 'Product'[Color] ) = "Red"
            || RELATED ( Customer[Country] ) = "United States"
    )
)

That pattern is correct but scans the whole fact table, so save it for when you truly need cross-table OR logic.

Power BI DAX filter in list

For "Region is West or East", don't chain ||. Use the IN operator with a list in curly braces:

West East Sales =
CALCULATE (
    [Total Sales],
    Sales[Region] IN { "West", "East" }
)
-- Result: 4,550 (West 2,350 + East 2,200)

The opposite, "not in list", wraps the condition in NOT:

Other Regions Sales =
CALCULATE (
    [Total Sales],
    NOT Sales[Region] IN { "West", "East" }
)
-- Result: 1,800 (North 1,300 + South 500)

Power BI DAX filter multiple values from a slicer

When the list of values comes from the user, you don't hard-code it. The slicer already filters the column, so the measure just reads it. VALUES returns the distinct values currently visible, which is handy for labels and for checks:

Selected Regions Label =
IF (
    ISFILTERED ( Sales[Region] ),
    CONCATENATEX ( VALUES ( Sales[Region] ), Sales[Region], ", ", Sales[Region], ASC ),
    "All regions"
)
-- Slicer on West and East: "East, West"

To combine a fixed list with whatever the user picked, use KEEPFILTERS (explained below) so the two lists intersect instead of the fixed list winning.

Power BI DAX filter contains text

There is no LIKE operator in DAX. For "contains", use CONTAINSSTRING, which is case-insensitive (CONTAINSSTRINGEXACT is the case-sensitive version):

Laptop-type Sales =
CALCULATE (
    [Total Sales],
    CONTAINSSTRING ( Sales[Product], "top" )
)
-- Result: 3,750 (all three Laptop orders)

For "starts with", use LEFT ( Sales[Product], 3 ) = "Lap". Keep in mind that plain = text comparison in DAX is also case-insensitive, so Sales[Region] = "west" still matches "West".

Filter by date range

For a fixed range, a Boolean filter on the date column is enough:

February Sales =
CALCULATE (
    [Total Sales],
    'Date'[Date] >= DATE ( 2026, 2, 1 ) && 'Date'[Date] <= DATE ( 2026, 2, 28 )
)
-- Result: 2,150 (1,300 + 150 + 700)

For a rolling window, a time intelligence function returns the dates for you. Anchor it on the last date that has sales, not on the end of the calendar:

Sales Last 30 Days =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( Sales[Date] ), -30, DAY )
)
-- Result at the grand total: 2,450 (Feb 17 to Mar 18: 700 + 500 + 1,250)

Anchoring on MAX('Date'[Date]) instead would start from December 31, 2026 and return blank. That is one of the most common "my last 30 days measure is empty" bugs.

How to filter a measure in Power BI

Here is where FILTER earns its place. Say you want sales from products that sold more than 900 in total. The condition uses a measure, and a Boolean filter can't reference a measure. So you build the list of qualifying products with FILTER over VALUES:

Sales of Top Products =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Sales[Product] ), [Total Sales] > 900 )
)
-- Product totals: Laptop 3,750, Phone 1,500, Desk 950, Chair 150
-- Result: 6,200 (Chair is dropped)

Why VALUES and not Sales? VALUES(Sales[Product]) has 4 rows; Sales could have 40 million. FILTER evaluates [Total Sales] once per row, and each measure call inside an iterator triggers context transition, so the smaller the table you iterate, the faster the measure. This is the same pattern Microsoft uses for "sales for profitable months".

Count how many products pass the test with the same table:

Products Over 900 =
COUNTROWS ( FILTER ( VALUES ( Sales[Product] ), [Total Sales] > 900 ) )
-- Result: 3

Filter with an iterator instead of CALCULATE

FILTER also feeds iterators directly. This average ignores small orders:

Avg Big Order =
AVERAGEX (
    FILTER ( Sales, Sales[Amount] > 1000 ),
    Sales[Amount]
)
-- Result: 1,250 (1,200 + 1,300 + 1,250, divided by 3)

A faster equivalent is CALCULATE ( AVERAGE ( Sales[Amount] ), Sales[Amount] > 1000 ). Both are correct; prefer the CALCULATE version on large tables.

Filter the visual by a measure (no DAX needed)

If you only want to hide rows in one visual, you may not need a new measure at all. In the Filters pane, drag the measure into Filters on this visual and set a condition like "is greater than 900". That filters the rows of the visual, while your DAX measure stays reusable everywhere.

Power BI DAX filter ALL, REMOVEFILTERS and ALLSELECTED

These functions remove filters instead of adding them. You need them for percent-of-total, ranking and "compare to all" measures.

ALL and REMOVEFILTERS

Both remove filters from the columns or table you name. Microsoft recommends REMOVEFILTERS when you only want to clear filters inside CALCULATE, because the name says what it does. ALL also works as a table function (it returns all rows), which REMOVEFILTERS does not.

Category % of Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Sales ) )
)
-- No slicer, Category on rows:
-- Tech: 5,250 / 6,350 = 82.7%   Furniture: 1,100 / 6,350 = 17.3%

REMOVEFILTERS ( Sales ) clears every filter on the Sales table. REMOVEFILTERS ( Sales[Category] ) would clear only the Category filter and keep the rest, such as a Region slicer.

ALLSELECTED

ALLSELECTED removes the filters coming from the visual's own rows but keeps the filters the user set outside it, like slicers. Use it when the percentages should add up to 100% of what the user selected:

Category % of Selected =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED ( Sales[Category] ) )
)
-- Region slicer on East + West (4,550 total):
-- Tech: 3,950 / 4,550 = 86.8%   Furniture: 600 / 4,550 = 13.2%

With the same East + West slicer, Category % of Total divides by all 6,350, so Tech shows 62.2% (3,950 / 6,350) and Furniture 9.4%. The rows no longer add up to 100%, because the denominator ignores the slicer. Neither is wrong; they answer different questions. Choose the function by asking: "100% of what?"

ALLEXCEPT

ALLEXCEPT(Sales, Sales[Region]) removes every filter on the Sales table except Region. It is handy for "share within my region" measures, but be careful: it only keeps filters on the listed columns, so slicers on other columns of that table are removed too.

KEEPFILTERS: stop your filter overwriting the slicer

Remember that a Boolean filter is really FILTER(ALL(column), ...). That ALL means it replaces any filter already on the column. Watch what happens with Region on the rows of a matrix:

West Sales =
CALCULATE ( [Total Sales], Sales[Region] = "West" )

West Sales Keep =
CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Region] = "West" ) )

West Sales Table =
CALCULATE ( [Total Sales], FILTER ( Sales, Sales[Region] = "West" ) )

Region	Total Sales	West Sales	West Sales Keep	West Sales Table
East	2,200	2,350	(blank)	(blank)
North	1,300	2,350	(blank)	(blank)
South	500	2,350	(blank)	(blank)
West	2,350	2,350	2,350	2,350
Total	6,350	2,350	2,350	2,350

The plain Boolean version shows 2,350 on every row because it throws away the row's Region filter. Sometimes that is what you want (a fixed benchmark line). Most of the time it isn't. KEEPFILTERS intersects the new filter with the existing one, giving the same result as the FILTER-over-table version but at Boolean speed. That is the exact rewrite Microsoft recommends.

When to skip KEEPFILTERS: benchmark measures ("always show West for comparison") and percent-of-total denominators, where overwriting is the whole point.

Power BI DAX filter table: calculated tables and queries

Because FILTER returns a table, you can also use it to create one. Go to Modeling > New table:

High Value Orders =
FILTER ( Sales, Sales[Amount] > 1000 )
-- Returns orders 1, 4 and 8

A calculated table is computed at refresh and stored in the model, so it does not react to slicers. Use it for helper tables (a list of key accounts, a bridge table), not for anything that should change on click. CALCULATETABLE does the same job with CALCULATE-style filter arguments: CALCULATETABLE ( Sales, Sales[Region] = "East" ).

Test filters in DAX query view

The fastest way to see what a FILTER returns is to run it as a query. Open DAX query view in Power BI Desktop (the icon in the left bar) and run:

EVALUATE
FILTER ( Sales, Sales[Region] = "East" )
ORDER BY Sales[Amount] DESC

You get the three East orders (1,250, 800, 150) as a grid. I debug almost every tricky filter this way before turning it into a measure: if the table is wrong, the measure will be wrong too.

Common errors, performance and best practices

These are the errors people paste into forums most often, with the fix for each.

Error: "A function 'PLACEHOLDER' has been used in a True/False expression"

The full message ends with "that is used as a table filter expression. This is not allowed." You wrote something like CALCULATE([Total Sales], [Margin %] > 0.3). A Boolean filter can't reference a measure. Fix: move the condition into FILTER over the smallest column that makes sense: FILTER(VALUES(Sales[Product]), [Margin %] > 0.3).

Error: "The expression contains columns from multiple tables"

Your Boolean filter mixes two tables, for example 'Product'[Color] = "Red" || Customer[Country] = "US". Fix: if you need AND, split it into two filter arguments. If you need OR, use FILTER over the fact table with RELATED, as shown in the OR section.

Error: "DAX comparison operations do not support comparing values of type Text with values of type Integer"

You compared a number column to a quoted value (Sales[Amount] > "1000") or a text column holding numbers to a number. Fix: remove the quotes, or fix the column's data type in Power Query so the model stores numbers as numbers.

Error: "A table of multiple values was supplied where a single value was expected"

You put FILTER (or VALUES) where DAX expects one value, like a card or an IF test. Fix: wrap the table in an aggregator such as COUNTROWS, SUMX or MAXX, or use SELECTEDVALUE when you want the single selected item.

The measure ignores the slicer

Not an error message, but the most common complaint. A Boolean filter on the same column as the slicer overwrites it. Fix: wrap it in KEEPFILTERS.

Performance: filter columns, not tables

Most slow filter measures share one pattern: FILTER(Sales, ...) on a big fact table. Rewrite in this order:

1. Can it be a Boolean filter on one column? Use that, with KEEPFILTERS if the slicer matters.
2. Does it need a measure? Iterate VALUES(Dim[Key]) or ALL(T[Col]), not the fact table.
3. Does it need several columns? Iterate only those columns: FILTER(ALL(Sales[Category], Sales[Region]), ...).
4. Store reused results in a VAR so they are calculated once.
5. Measure the before and after with Performance analyzer in Power BI Desktop instead of guessing.

Best practices worth keeping

• Name filtered measures after the filter ("Sales West", "Sales Big Orders") so report users know what they see.
• Build one base measure (Total Sales) and filter it, rather than repeating SUM logic in every measure.
• Use a proper date table for anything time-based; never filter dates on the fact table if a date dimension exists.
• Comment the "why" when you use FILTER instead of a Boolean filter, so the next developer doesn't "optimise" it back.
• Highlight filtered numbers consistently. A theme with a clear accent colour for "filtered" KPIs helps; you can set one up in the free Power BI theme builder.

Wrap-up and next steps

A Power BI DAX filter comes down to one habit: start with the simplest filter that works. A Boolean condition inside CALCULATE handles most measures; add KEEPFILTERS when the slicer must still count; switch to FILTER only for measures, cross-table logic or OR conditions across tables; and use ALL, REMOVEFILTERS or ALLSELECTED when you need to step out of the current context.

Next steps: rebuild the sample model, paste the West Sales trio and watch the matrix change, then rewrite one slow FILTER measure in your own report using the checklist above. If you want a head start on layout, our Power BI templates come with measures and themes already wired up.

FAQ

What is the difference between CALCULATE and FILTER in DAX?

CALCULATE evaluates an expression in a changed filter context and returns a value. FILTER returns a table of rows that meet a condition. You usually pass FILTER's table into CALCULATE when a simple Boolean filter can't express the condition.

How do I filter on multiple conditions in Power BI DAX?

For AND, add separate filter arguments to CALCULATE, like Sales[Category] = "Tech", Sales[Region] = "West". For OR on columns of the same table, use || inside one argument. For OR across different tables, use FILTER over the fact table with RELATED.

How do I filter multiple values in a DAX measure?

Use the IN operator with a list: Sales[Region] IN { "West", "East" }. Wrap it in NOT for "not in list". If the values come from a slicer, the slicer already filters the column, so you don't need to list them.

Why does my CALCULATE filter ignore the slicer?

A Boolean filter on a column replaces any existing filter on that same column, including the slicer. Wrap the condition in KEEPFILTERS, for example KEEPFILTERS(Sales[Region] = "West"), so it intersects with the slicer instead.

Can I use a measure inside a DAX filter?

Not in a Boolean filter argument; DAX returns the "PLACEHOLDER in a True/False expression" error. Use FILTER over a small table instead, such as FILTER(VALUES(Sales[Product]), [Total Sales] > 900).

How do I filter text that contains a word in DAX?

Use CONTAINSSTRING inside CALCULATE: CALCULATE([Total Sales], CONTAINSSTRING(Sales[Product], "top")). It is case-insensitive; use CONTAINSSTRINGEXACT for a case-sensitive match.

Is FILTER slow in Power BI?

FILTER is an iterator, so its cost grows with the number of rows it scans. Over a small column (VALUES or ALL of one column) it is fast; over a large fact table it can be slow. Prefer Boolean filters and iterate the smallest table that gives the right answer.

What is the difference between ALL and ALLSELECTED?

ALL removes every filter on the named columns or table, including slicers. ALLSELECTED removes only the filters coming from the visual itself and keeps the user's slicer selections, so totals add up to 100% of what is selected.