DAX Filter Functions

Filter functions control which data is evaluated during calculations. They are fundamental to writing powerful and flexible DAX measures.

FILTER()

Returns a table that has been filtered based on a condition. Often used inside other functions like CALCULATE, SUMX, COUNTROWS.

Syntax: FILTER(Table, FilterExpression)

Examples:

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Amount] > 1000))

Electronics Revenue =
SUMX(
    FILTER(Sales, RELATED(Products[Category]) = "Electronics"),
    Sales[Quantity] * Sales[UnitPrice]
)

Key Points: • FILTER returns a table (not a single value) • It iterates row by row (can be slow on large tables) • Best used when you need complex conditions that CALCULATE alone can't handle

ALL()

Removes all filters from a table or column. Returns the entire table/column regardless of any slicers or filters applied.

Syntax: • ALL(Table) — removes all filters from a table • ALL(Column) — removes filters from a specific column • ALL(Column1, Column2) — removes filters from multiple columns

Examples:

Percentage of Total:

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

Grand Total regardless of filters:

Grand Total = CALCULATE([Total Sales], ALL(Products))

ALLEXCEPT()

Removes all filters except from specified columns. Useful when you want to keep some context.

Syntax: ALLEXCEPT(Table, Column1, Column2, ...)

Example:

Category % =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLEXCEPT(Sales, Products[Category]))
)

This calculates the percentage within each category.

RELATED()

Fetches a value from a related table (follows a many-to-one relationship). Works like VLOOKUP in Excel.

Syntax: RELATED(Column)

Example: If Sales table has ProductID and Products table has ProductID + ProductName:

Product Name = RELATED(Products[ProductName])

Key Points: • Works in calculated columns (row context) • Follows the relationship from the "many" side to the "one" side • The relationship must already exist in the data model

RELATEDTABLE()

Returns the entire related table (follows a one-to-many relationship). The opposite direction of RELATED.

Syntax: RELATEDTABLE(Table)

Example — Count of orders per customer:

Order Count = COUNTROWS(RELATEDTABLE(Sales))

(Used as a calculated column in the Customers table)

Comparison Table