Co-authored by

What Are DAX Filter Functions and Why Do They Matter?

Quick answer: DAX filter functions control which rows are visible to a calculation by manipulating the filter context — they are the mechanism behind percentage-of-total measures, conditional aggregations, and every pattern that requires overriding user selections in Power BI.

If CALCULATE is the most important function in DAX, then filter functions are the tools you hand to CALCULATE to do its job. Functions like ALL, ALLEXCEPT, FILTER, and REMOVEFILTERS determine which filters get removed, preserved, or applied before a measure evaluates. Without them, every calculation would be locked to whatever a slicer or visual happens to show.

The Microsoft Learn filter functions reference lists over 25 functions in this category, but in practice six functions handle the vast majority of business requirements: ALL, ALLEXCEPT, FILTER, REMOVEFILTERS, VALUES, and SELECTEDVALUE. This guide covers each one with syntax, behaviour, practical examples, and the performance considerations that matter when building production Power BI models.

If you are coming from Excel and encountering DAX for the first time, our Excel to Power BI migration guide covers the broader transition, including how DAX's filter context model differs from Excel's flat-cell approach.

How Does the ALL Function Work in DAX?

Quick answer: ALL returns all rows in a table or all distinct values in one or more columns, ignoring any active filters — when used inside CALCULATE, it acts as a filter modifier that removes filters rather than returning a table.

ALL has a dual identity in DAX that causes confusion. It behaves differently depending on where you use it:

• As a table function (standalone or inside an iterator): ALL returns a table with all rows, ignoring filters. Useful for iterating over a complete set of values.
• As a CALCULATE filter modifier: ALL removes existing filters from the specified table or columns. It does not return a table in this context — it modifies the filter context.

Syntax

ALL( [<table> | <column> [, <column> [, ...]]] )

The argument must be a reference to a base table or one or more base columns. You cannot pass table expressions or calculated columns to ALL.

ALL on a Column: Percentage of Grand Total

The most common use of ALL is building percentage-of-total measures. Here, ALL removes the filter on Geography[Country] so the denominator always returns the grand total:

Revenue % of Total =
VAR CurrentRevenue = SUM( Sales[Sales Amount] )
VAR TotalRevenue =
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        ALL( 'Geography'[Country] )
    )
RETURN
DIVIDE( CurrentRevenue, TotalRevenue )

When a matrix shows revenue by country, each row's denominator ignores the country filter and returns total revenue across all countries. The numerator respects the current filter context, producing a percentage.

ALL on a Table: Removing All Filters from a Dimension

Passing an entire table to ALL removes all filters from that table and from any related tables reachable through many-to-one relationships:

Revenue % of All Products =
DIVIDE(
    SUM( Sales[Sales Amount] ),
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        ALL( 'Product' )
    )
)

This removes filters from every column in the Product table — category, subcategory, colour, brand — so the denominator reflects total revenue regardless of product selections.

When Should You Use REMOVEFILTERS Instead of ALL?

Quick answer: REMOVEFILTERS is the recommended replacement for ALL when your intent is to remove filters inside CALCULATE — it has identical behaviour but makes the code's intent explicit, since REMOVEFILTERS can only remove filters and never return a table.

Microsoft introduced REMOVEFILTERS in 2019 specifically to address the ambiguity of ALL's dual role. The Microsoft Learn REMOVEFILTERS documentation states: "If the REMOVEFILTERS function is supported by your tool, it's better to use it to remove filters."

Syntax

REMOVEFILTERS( [<table> | <column> [, <column> [, ...]]] )

The syntax is identical to ALL. The difference is that REMOVEFILTERS can only be used as a CALCULATE filter modifier — it cannot appear standalone as a table expression. This constraint makes your DAX easier to read and review: when you see REMOVEFILTERS, you know immediately that filters are being removed.

REMOVEFILTERS with No Arguments

Calling REMOVEFILTERS with no arguments removes all filters from the entire model:

Revenue % of Everything =
DIVIDE(
    SUM( Sales[Sales Amount] ),
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        REMOVEFILTERS()
    )
)

The denominator ignores every slicer, every visual filter, and every row/column header — it returns the absolute grand total across all dimensions. Use this sparingly, as it produces a denominator that is often too broad to be meaningful.

Column-Specific REMOVEFILTERS

More commonly, you remove filters from specific columns to create targeted comparisons:

Revenue % by Region =
DIVIDE(
    SUM( Sales[Sales Amount] ),
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        REMOVEFILTERS( 'Geography'[Country] )
    )
)

This keeps filters on year, product category, and any other dimension intact — only the country filter is removed. The result is each country's share of revenue within the currently selected year and category.

How Does ALLEXCEPT Differ from ALL?

Quick answer: ALLEXCEPT removes all filters from a table except for the columns you specify — it is a shorthand for removing most filters while preserving specific ones, commonly used for percentage-of-parent calculations.

Syntax

ALLEXCEPT( <table>, <column> [, <column> [, ...]] )

The first argument must be a base table reference. The remaining arguments are columns from that table whose filters you want to preserve.

Percentage of Parent Category

A classic ALLEXCEPT pattern computes each subcategory's share of its parent category:

% of Category =
VAR CurrentSales = SUM( Sales[Sales Amount] )
VAR CategorySales =
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        ALLEXCEPT( 'Product', 'Product'[Category] )
    )
RETURN
DIVIDE( CurrentSales, CategorySales )

In a matrix showing Category and Subcategory, ALLEXCEPT removes the filter on Subcategory (and every other Product column) while keeping the Category filter. The denominator returns total sales for the current category, and the result is each subcategory's share.

The ALLEXCEPT Caveat

SQLBI's analysis of ALLEXCEPT versus ALL and VALUES highlights a critical subtlety: ALLEXCEPT removes filters from the expanded table, which includes all tables reachable through many-to-one relationships. This means ALLEXCEPT can inadvertently remove filters from related dimension tables that you intended to keep.

For more predictable behaviour, SQLBI recommends the REMOVEFILTERS + VALUES pattern:

% of Category (Safer) =
VAR CurrentSales = SUM( Sales[Sales Amount] )
VAR CategorySales =
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        REMOVEFILTERS( 'Product' ),
        VALUES( 'Product'[Category] )
    )
RETURN
DIVIDE( CurrentSales, CategorySales )

Here, REMOVEFILTERS clears all filters from the Product table, and VALUES re-applies only the currently visible Category values as a filter. This approach is explicit about what gets removed and what gets restored, and it works correctly regardless of which columns appear in the report.

How Does the FILTER Function Work as an Iterator?

Quick answer: FILTER is an iterator function that evaluates a Boolean expression row by row against a table and returns only the rows where the expression is TRUE — it is the only way to apply complex, measure-based, or multi-column conditions inside CALCULATE.

Syntax

FILTER( <table>, <filter_expression> )

FILTER scans every row of the table argument, evaluates the filter expression in a row context, and returns a table containing only the matching rows. That returned table can then be used as a CALCULATE filter argument, as input to another iterator, or anywhere DAX expects a table.

When FILTER Is Required

Microsoft's best practice guidance is clear: prefer Boolean filter expressions in CALCULATE wherever possible. Use FILTER only when Boolean expressions cannot do the job. The three scenarios where FILTER is necessary:

1. Filtering by a measure value:

Boolean filter arguments in CALCULATE cannot reference measures. If you need to filter based on a calculated value, FILTER is the only option:

Revenue from Profitable Months =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER(
        ALL( 'Date'[Month], 'Date'[Year] ),
        [Profit] > 0
    )
)

This calculates revenue only for month/year combinations that had positive profit. The [Profit] measure reference triggers context transition for each row — which is exactly why FILTER is needed here.

2. Complex multi-column conditions with OR logic:

Boolean filter arguments in CALCULATE combine with AND logic. For OR conditions across different columns, use FILTER:

Priority Sales =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER(
        ALL( 'Product'[Category], 'Customer'[Tier] ),
        'Product'[Category] = "Enterprise"
            || 'Customer'[Tier] = "Platinum"
    )
)

3. Comparing columns from different tables:

Boolean filter expressions can only reference columns from a single table. Cross-table comparisons require FILTER with an appropriate table expression.

Performance: FILTER vs Boolean Expressions

The performance difference is not theoretical. Boolean expressions like 'Product'[Color] = "Red" are optimised by the VertiPaq storage engine into efficient column scans. FILTER, by contrast, iterates row by row in the formula engine, which is significantly slower on large tables.

The key rule from SQLBI's filter arguments guide: always filter columns, never filter entire tables. Instead of FILTER( 'Product', 'Product'[Color] = "Red" ), use FILTER( ALL( 'Product'[Color] ), 'Product'[Color] = "Red" ) — or better yet, the Boolean expression 'Product'[Color] = "Red" directly.

How Do VALUES, SELECTEDVALUE, and HASONEVALUE Work?

Quick answer: VALUES returns the distinct values visible in the current filter context, SELECTEDVALUE returns a single value when only one exists (otherwise a default), and HASONEVALUE returns TRUE/FALSE to test whether exactly one value is in the filter context — together they enable dynamic, context-aware measures.

These three functions are not filter modifiers like ALL or REMOVEFILTERS. They read the current filter context rather than changing it. But they appear constantly alongside filter functions in practical DAX patterns.

VALUES: The Distinct Values in Context

VALUES( <column_or_table> )

VALUES returns a single-column table of distinct values that are currently visible. When used inside CALCULATE, it re-applies a column's current filter after REMOVEFILTERS clears it — the REMOVEFILTERS + VALUES pattern described earlier.

VALUES is also essential for building dynamic measures that adapt based on slicer selections:

Dynamic KPI =
SWITCH(
    SELECTEDVALUE( 'KPI Selector'[KPI Name] ),
    "Revenue", [Total Revenue],
    "Profit", [Total Profit],
    "Units", [Total Units Sold],
    [Total Revenue]
)

SELECTEDVALUE: Safe Single-Value Extraction

SELECTEDVALUE( <column> [, <alternate_result>] )

SELECTEDVALUE returns the value of a column when exactly one value is in the filter context. If multiple values are present, it returns the alternate result (or BLANK if no alternate is specified). The Microsoft Learn best practice recommends SELECTEDVALUE over the older IF( HASONEVALUE(...), VALUES(...) ) pattern — it is functionally equivalent but more concise:

-- Modern (recommended)
Selected Country = SELECTEDVALUE( 'Geography'[Country], "Multiple" )
 
-- Legacy equivalent (avoid)
Selected Country =
IF(
    HASONEVALUE( 'Geography'[Country] ),
    VALUES( 'Geography'[Country] ),
    "Multiple"
)

HASONEVALUE: Conditional Logic Based on Filter Context

HASONEVALUE( <column> )

HASONEVALUE returns TRUE when exactly one distinct value exists in the filter context for the specified column. It is useful for measures that should behave differently at summary versus detail levels:

Revenue Display =
IF(
    HASONEVALUE( 'Geography'[Country] ),
    FORMAT( [Total Revenue], "#,##0" ),
    FORMAT( [Total Revenue], "#,##0" ) & " (Total)"
)

While SELECTEDVALUE has largely replaced the HASONEVALUE + VALUES combination, HASONEVALUE remains useful when you need the Boolean test itself — for example, in SWITCH or IF logic that controls measure behaviour at different levels of a hierarchy.

What Are Common DAX Filter Function Patterns for Business Measures?

Quick answer: The most frequently used patterns are percentage of grand total, percentage of parent, conditional filtering by measure values, and dynamic top-N filtering — each built from combinations of ALL, REMOVEFILTERS, ALLEXCEPT, FILTER, and VALUES.

Pattern 1: Percentage of Grand Total

Remove all filters from the relevant dimension so the denominator always returns the total:

Revenue % of Grand Total =
VAR CurrentRevenue = SUM( Sales[Sales Amount] )
VAR GrandTotal =
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        REMOVEFILTERS( 'Geography' )
    )
RETURN
DIVIDE( CurrentRevenue, GrandTotal )

Pattern 2: Percentage of Parent (Hierarchy)

Use REMOVEFILTERS + VALUES for a robust parent-percentage calculation:

% of Region =
VAR CurrentRevenue = SUM( Sales[Sales Amount] )
VAR RegionRevenue =
    CALCULATE(
        SUM( Sales[Sales Amount] ),
        REMOVEFILTERS( 'Geography' ),
        VALUES( 'Geography'[Region] )
    )
RETURN
DIVIDE( CurrentRevenue, RegionRevenue )

This works in a matrix with Region and Country. For each country row, REMOVEFILTERS clears geography filters, and VALUES restores the current Region filter — so the denominator returns the region total.

Pattern 3: Conditional Filtering by Measure

Show revenue only for products that have been sold more than 100 times:

Revenue (High Volume Products) =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER(
        ALL( 'Product'[Product Name] ),
        CALCULATE( SUM( Sales[Quantity] ) ) > 100
    )
)

FILTER iterates over all product names, evaluates total quantity for each (via context transition), and returns only those exceeding 100. CALCULATE then computes revenue restricted to that filtered set.

Pattern 4: Dynamic Top-N with FILTER and RANKX

Show revenue contributed by only the top 10 products:

Top 10 Product Revenue =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER(
        ALL( 'Product'[Product Name] ),
        RANKX(
            ALL( 'Product'[Product Name] ),
            CALCULATE( SUM( Sales[Sales Amount] ) )
        ) <= 10
    )
)

What Changed for DAX Filter Functions in 2025 and 2026?

Quick answer: The core filter functions — ALL, ALLEXCEPT, FILTER, REMOVEFILTERS — are unchanged in syntax and behaviour, but the 2025 introduction of user-defined functions and SUMMARIZECOLUMNS in measures creates new ways to compose filter patterns.

The DAX language saw its most significant update since variables were introduced in 2015, as SQLBI documented in their 2025 year-end review:

User-defined functions (UDFs) entered public preview in September 2025 and allow you to encapsulate reusable filter patterns into named, parameterised functions. A common pattern — like the REMOVEFILTERS + VALUES percentage calculation — can now be defined once and called across dozens of measures without duplication. UDFs are available in preview within Microsoft Fabric and are expected to reach general availability during 2026. The daxlib.org repository provides a free, open-source collection of model-independent DAX functions ready for import.

SUMMARIZECOLUMNS in measures became possible in 2025, after being restricted to query-only usage for years. This expands the ability to create complex filtered and grouped results within measure definitions, though most standard filter patterns still use CALCULATE with the filter functions covered in this guide.

21 new DAX functions were introduced in 2025, including week-related time intelligence functions and type-checking functions. None of these change how ALL, ALLEXCEPT, FILTER, or REMOVEFILTERS behave, but they expand the library of expressions you can combine with filter functions.

No breaking changes or deprecations affect the filter functions covered here. Code written with REMOVEFILTERS, ALL, ALLEXCEPT, and FILTER continues to work identically in the February 2026 version of Power BI Desktop.

Frequently Asked Questions

Q: What is the difference between ALL and REMOVEFILTERS in DAX?

When used as a CALCULATE filter modifier, ALL and REMOVEFILTERS are functionally identical — both remove filters from the specified table or columns. The difference is that ALL can also be used as a standalone table function (returning all rows), while REMOVEFILTERS can only be used inside CALCULATE to remove filters. Microsoft recommends REMOVEFILTERS for clarity when your intent is to remove filters, because it eliminates the ambiguity of ALL's dual role.

Q: When should I use ALLEXCEPT instead of REMOVEFILTERS with VALUES?

Use ALLEXCEPT when you want a concise shorthand for removing all filters from a table except specific columns, and you are confident that the expanded table behaviour will not cause issues. However, SQLBI recommends the REMOVEFILTERS + VALUES pattern as the safer default, because ALLEXCEPT removes filters from the expanded table (including related dimension tables), which can produce unexpected results when your model has complex relationships. The REMOVEFILTERS + VALUES approach is explicit about what gets removed and what gets restored.

Q: Why does Microsoft say to avoid using FILTER as a filter argument in CALCULATE?

Microsoft's best practice article recommends Boolean expressions over FILTER for simple column-value comparisons because Boolean expressions are optimised by the VertiPaq storage engine into efficient column scans, while FILTER iterates row by row in the formula engine. On large tables, the performance difference is significant. Use FILTER only when you need to reference measures, apply OR logic across columns, or compare columns from different tables — scenarios where Boolean expressions cannot work.

Q: How do I calculate percentage of parent category in DAX?

Use the REMOVEFILTERS + VALUES pattern for a robust parent-percentage measure: DIVIDE( SUM( Sales[Amount] ), CALCULATE( SUM( Sales[Amount] ), REMOVEFILTERS( 'Product' ), VALUES( 'Product'[Category] ) ) ). This clears all product filters, then re-applies only the current category as a filter, so the denominator returns the category total. The result is each subcategory's share of its parent category, and the pattern works correctly regardless of which other columns appear in the visual.

Q: What is the difference between VALUES and SELECTEDVALUE in DAX?

VALUES returns a single-column table of all distinct values visible in the current filter context. SELECTEDVALUE returns a scalar — the single value from a column when exactly one exists, or a default value when multiple values are present. SELECTEDVALUE is equivalent to IF( HASONEVALUE( column ), VALUES( column ), default ) but Microsoft recommends it for conciseness. Use VALUES when you need a table (for CALCULATE filter arguments or iterations); use SELECTEDVALUE when you need a single value for display or conditional logic.

Q: Can I use FILTER to reference a measure inside CALCULATE?

Yes, and this is the primary reason FILTER exists as a CALCULATE filter argument. Boolean filter expressions in CALCULATE cannot reference measures — only column values. When you need to filter based on a calculated result (such as only including months where profit exceeded a threshold), wrap the condition in FILTER: CALCULATE( [Revenue], FILTER( ALL( 'Date'[Month] ), [Profit] > 0 ) ). Be aware that FILTER evaluates the measure for every row via context transition, so always filter on the narrowest column set possible rather than entire tables.

Q: How does filter context differ from row context in DAX?

Filter context is the set of active filters applied to the data model before a DAX expression evaluates — created by slicers, visual axes, and CALCULATE. Row context is the "current row" during iteration, created by iterators (SUMX, FILTER, AVERAGEX) and calculated columns. The critical distinction from SQLBI's documentation: row context does not filter the model, and it does not propagate through relationships. Only filter context does. CALCULATE bridges the two through context transition — converting a row context into an equivalent filter context.

Q: Do DAX filter functions work in DirectQuery mode?

ALL, ALLEXCEPT, FILTER, REMOVEFILTERS, VALUES, and SELECTEDVALUE all work in DirectQuery measures and visuals. However, they are not supported in DirectQuery mode for calculated columns or row-level security (RLS) rules. This is a DirectQuery limitation, not a function limitation. For GCC enterprises using DirectQuery against on-premises databases such as SAP or Oracle, this means RLS rules must use simpler DAX expressions that avoid these functions, while measures can use them freely.