Daily DAX : Day 188 ALLCROSSFILTERED

 The ALLCROSSFILTERED function in Power BI DAX removes all filters from a table, including those applied by cross-filtering from other tables in the model, while preserving relationships. Unlike ALL, which removes filters only from the specified table or columns, ALLCROSSFILTERED clears both direct filters and those inherited through relationships, making it particularly useful in complex data models with multiple related tables.

Syntax

dax

ALLCROSSFILTERED(<table>)

    table: The table from which to remove all filters, including cross-filters.

Return Value

Returns the table with all filters removed, including those from related tables via cross-filtering.

Key Characteristics

    Removes all filters (slicers, page filters, visual filters, and cross-filters from relationships).

    Preserves the data model’s relationships, so calculations respect the structure of the model.

    Typically used in measures or calculated columns where you need to perform calculations over the entire dataset, ignoring external filter contexts.

Use Case

ALLCROSSFILTERED is useful when you need to calculate a value (e.g., a total or percentage) across all rows of a table, regardless of filters applied through slicers, visuals, or related tables. A common scenario is calculating a percentage of a grand total in a report with multiple filters.

Example Scenario

Suppose you have a sales data model with two related tables:

    Sales: Contains sales amounts and references a Product table.

    Product: Contains product categories.

You want to calculate the percentage of total sales for each product category, ignoring any filters applied to the Product table (e.g., a slicer filtering by category).

Example DAX Measure

dax

Total Sales = SUM(Sales[Amount])

Total Sales All = 

CALCULATE(

    [Total Sales],

    ALLCROSSFILTERED(Product)

)

Sales Percentage = 

DIVIDE(

    [Total Sales],

    [Total Sales All],

    0

)

    Total Sales: Calculates the sum of sales amounts under the current filter context.

    Total Sales All: Uses ALLCROSSFILTERED(Product) to remove all filters on the Product table, including those from slicers or cross-filtering, ensuring the total reflects all product categories.

    Sales Percentage: Divides the filtered sales by the unfiltered total to get the percentage of total sales.

Result

If a slicer filters the report to show only "Electronics" products, the Sales Percentage measure still calculates the percentage of sales for "Electronics" relative to the total sales across all categories, because ALLCROSSFILTERED(Product) ignores the slicer and cross-filters.

When to Use

    Complex models: When calculations must ignore filters from multiple related tables.

    Percentage calculations: To compute ratios or percentages against a grand total, regardless of applied filters.

    Dynamic reports: When users apply slicers or filters, but you need a consistent baseline (e.g., total sales across all data).

Notes

    Use cautiously, as removing all filters can lead to performance issues in large datasets.

    Ensure the table passed to ALLCROSSFILTERED is the one where cross-filtering needs to be ignored.

    If you only need to remove filters from specific columns or a single table without affecting relationships, consider ALL or ALLEXCEPT instead.