ALL & FILTER Functions

The ALL function is used to remove filters from a table or a specific column. It is commonly used inside CALCULATE to ignore existing filters and return complete results.

Syntax

ALL(Table)

or

ALL(Table[Column])

Example 1 Removing All Filters from a Column

Total Sales All Years =
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Sales[Year])
)

This measure ignores any filter applied to the Year column and returns total sales for all years.

Example 2 Removing All Filters from a Table

Grand Total Sales =
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Sales)
)

This removes all filters from the Sales table.

When to Use ALL

• To calculate grand totals
• To create percentage of total measures
• To ignore slicer filters
• For comparison against overall totals

FILTER Function

The FILTER function returns a filtered table based on a condition. It is used when you need more complex filtering logic.

Syntax

FILTER(Table, condition)

Example 1 Simple Filter

High Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 10000)
)

This calculates total sales only where the Amount is greater than 10,000.

Example 2 Multiple Conditions

Sales 2024 High Value =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        Sales,
        Sales[Year] = 2024 &&
        Sales[Amount] > 10000
    )
)

This applies multiple logical conditions inside FILTER.

ALL vs FILTER

ALL removes filters from a table or column.

FILTER applies custom filtering conditions to a table.

ALL is used to ignore filters.
FILTER is used to apply detailed or complex filters.

Why These Functions Are Important

• Used in advanced DAX calculations
• Essential for percentage calculations
• Required for dynamic comparisons
• Help control filter context precisely

Conclusion

ALL and FILTER are powerful DAX functions that give you full control over filter context. Understanding how and when to use them is essential for building advanced and optimized Power BI reports.