Daily DAX : Day 354 KEEPFILTERS

Power BI DAX: KEEPFILTERS Function

The KEEPFILTERS function in Power BI DAX (Data Analysis Expressions) is used to modify how filters are applied in a calculation, ensuring that existing filters are preserved and combined with any new filters specified in the expression.

Syntax

KEEPFILTERS(<expression>)

• <expression>: The DAX expression to evaluate, typically a calculation like CALCULATE.

Purpose

KEEPFILTERS ensures that the existing filter context (from slicers, visuals, or other filters) is not overridden but is instead combined with any new filters applied within a CALCULATE function. Without KEEPFILTERS, CALCULATE may replace the existing filter context entirely.

How It Works

• By default, CALCULATE overwrites the filter context for the columns specified in its filter arguments.
• KEEPFILTERS modifies this behavior by applying new filters as an intersection (AND) with the existing filter context, preserving the original filters.

Use Case

Scenario: You have a sales report with a slicer for the year 2023, and you want to calculate sales for a specific product category while keeping the year filter intact.

Without KEEPFILTERS:

Sales_Amount = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Electronics")

This may ignore the year 2023 filter from the slicer, calculating sales for "Electronics" across all years.

With KEEPFILTERS:

Sales_Amount = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Category] = "Electronics"))

This ensures the year 2023 filter is preserved, so the calculation only includes sales for "Electronics" in 2023.

Example

Assume a table Sales with columns Year, Category, and Amount. A report has a slicer set to Year = 2023.

• Measure without KEEPFILTERS:

Total_Electronics = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Electronics")

Result: Sums sales for "Electronics" across all years, ignoring the 2023 slicer.

• Measure with KEEPFILTERS:

Total_Electronics_KF = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Category] = "Electronics"))

Result: Sums sales for "Electronics" only in 2023, respecting the slicer.

When to Use

• When you want to respect existing filters (e.g., from slicers or visuals) while adding new filters.
• Common in reports where users dynamically filter data, and calculations need to honor those filters.
• Useful in complex models to avoid unintended filter overrides.

Key Notes

• KEEPFILTERS is typically used within CALCULATE or CALCULATETABLE.
• It ensures additive filtering rather than replacing the filter context.
• Improves report accuracy when user interactions (like slicers) must influence calculations.