DAX Filter Functions Handbook — InsightsMastery Academy

1. Introduction to Filter Context

The filter and value functions in DAX are among the most powerful. They differ greatly from Excel functions. Lookup functions work with tables and relationships. Filtering functions let you manipulate data context to create dynamic calculations.

Filter context is the set of filters active when a formula is evaluated — from slicers, visual filters, row/column labels, and explicit filters you add with CALCULATE.

💡 Core principle
CALCULATE evaluates an expression in a modified filter context. Almost every advanced measure uses CALCULATE together with filter arguments or filter-modifier functions (ALL, REMOVEFILTERS, KEEPFILTERS, etc.).

2. CALCULATE & CALCULATETABLE

CALCULATE evaluates an expression in a modified filter context. CALCULATETABLE does the same for an expression that returns a table.

CALCULATE ( <expression> [, <filter1>] [, <filter2>] ... )
CALCULATETABLE ( <table expression> [, <filter1>] [, <filter2>] ... )

Filter arguments can be:

Boolean filter expressions — Product[Color] = "Red"
Table filter expressions — FILTER ( Product, ... ) or ALL ( Product )
Filter modification functions — REMOVEFILTERS, ALL, KEEPFILTERS, USERELATIONSHIP, …

Examples

// Boolean filter
Sales Red =
CALCULATE ( [Total Sales], Product[Color] = "Red" )

// Multiple filters (AND)
Sales Red Bikes =
CALCULATE (
    [Total Sales],
    Product[Color] = "Red",
    Product[Category] = "Bikes"
)

// Table expression
High Value Customers =
CALCULATETABLE (
    Customer,
    FILTER ( Customer, [Total Sales] > 10000 )
)

3. Removing Filters — ALL Family & REMOVEFILTERS

ALL

Returns all rows in a table, or all values in a column, ignoring filters that might have been applied.

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

CALCULATE ( [Total Sales], ALL ( Product ) )
CALCULATE ( [Total Sales], ALL ( Product[Color] ) )

ALLEXCEPT

Removes all context filters on a table except filters on the columns you specify.

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

Sales By Year Only =
CALCULATE (
    [Total Sales],
    ALLEXCEPT ( 'Date', 'Date'[Year] )
)

REMOVEFILTERS

Clears filters from the specified tables or columns. Preferred modern alternative for many ALL scenarios.

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

Sales All Products =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )

ALLSELECTED

Removes context filters from columns and rows in the current query, while retaining other context filters or explicit filters. Ideal for “% of visual total” patterns.

% of Visual Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED () )
)

ALLNOBLANKROW / ALLCROSSFILTERED

ALLNOBLANKROW — like ALL but excludes the blank row from relationships. ALLCROSSFILTERED — clears all filters that apply to a table, including via cross-filtering.

4. FILTER Function

Returns a table that is a subset of another table or expression. The condition is evaluated row by row.

FILTER ( <table>, <filter expression> )

Sales Over 1000 =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Amount] > 1000 )
)

⚠ Prefer boolean filters when possible
Inside CALCULATE, a boolean filter such as Sales[Amount] > 1000 is often more efficient than FILTER ( Sales, Sales[Amount] > 1000 ). Use FILTER when you need complex row logic.

5. KEEPFILTERS

Normally a new filter on a column overwrites existing filters on that column. KEEPFILTERS adds the filter without removing the existing ones (intersection).

KEEPFILTERS ( <expression> )

Sales Red Keeping Other Product Filters =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Product[Color] = "Red" )
)

6. ALLSELECTED & SELECTEDVALUE

SELECTEDVALUE returns the value when the column is filtered to exactly one distinct value; otherwise it returns an alternate result (or blank).

SELECTEDVALUE ( <columnName> [, <alternateResult>] )

Selected Color =
SELECTEDVALUE ( Product[Color], "Multiple colors" )

Title Label =
"Sales for " & SELECTEDVALUE ( 'Date'[Year], "All Years" )

7. LOOKUPVALUE

Returns the value in result_column for the row that meets all search conditions. Useful when RELATED is not available.

LOOKUPVALUE (
    <result_columnName>,
    <search_columnName>, <search_value>
    [, <search2> ...]
    [, <alternateResult>]
)

Product Category Name =
LOOKUPVALUE (
    Product[Category],
    Product[ProductKey], Sales[ProductKey]
)

8. Other Filter Functions

EARLIER / EARLIEST — outer row context (legacy; prefer variables)
INDEX, OFFSET, WINDOW, RANGE — position-based rows in a partition/axis
RANK, ROWNUMBER — ranking within an interval
MOVINGAVERAGE, RUNNINGSUM — running calculations along an axis
FIRST, LAST, NEXT, PREVIOUS — visual matrix navigation
PARTITIONBY, ORDERBY, MATCHBY — define partitions and order for window functions
LOOKUP, LOOKUPWITHTOTALS — visual calculation lookups

9. Practical Patterns

// % of grand total
Sales % of Grand Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( Sales ) )
)

// % of visual total
Sales % of Visual =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED () )
)

// Clear one table, keep everything else
Sales Ignoring Product Filters =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )

// Rank products by sales
Product Rank =
RANKX (
    ALL ( Product[ProductName] ),
    [Total Sales],
    ,
    DESC
)

10. Quick Reference Table

Function	Purpose
CALCULATE	Evaluate expression in modified filter context
CALCULATETABLE	Same for a table expression
FILTER	Return subset of a table (row condition)
ALL	Remove filters from table or column(s)
ALLEXCEPT	Remove all filters on table except listed columns
ALLSELECTED	Remove filters from current query; keep outer ones
REMOVEFILTERS	Clear filters (modern ALL alternative)
KEEPFILTERS	Add filter without overwriting existing ones
ALLNOBLANKROW	ALL without the relationship blank row
ALLCROSSFILTERED	Clear all filters reaching a table
SELECTEDVALUE	Single selected value or alternate
LOOKUPVALUE	Lookup value by search conditions
EARLIER / EARLIEST	Outer row context (prefer VAR)

11. Official Reference

This handbook follows the official Microsoft documentation. Always check Microsoft Learn for the latest syntax and remarks.

Primary source:
https://learn.microsoft.com/en-us/dax/filter-functions-dax

Related pages: CALCULATE, ALL, ALLEXCEPT, FILTER, REMOVEFILTERS, KEEPFILTERS, ALLSELECTED, SELECTEDVALUE, LOOKUPVALUE.