DAX

Dax Function: KEEPFILTERS

Q: How does KEEPFILTERS differ from REMOVEFILTERS?

KEEPFILTERS adds to the existing filters, whereas REMOVEFILTERS clears them.

Category: Filter Functions

The KEEPFILTERS function in Power BI is a DAX function used to ensure that existing filters in a query are preserved when additional filters are applied. This function is particularly useful in scenarios where filter context manipulation is required without overriding existing filters.

Purpose

The KEEPFILTERS function allows:

Preservation of existing filters in the evaluation context.
Combination of multiple filtering criteria without removing prior filters.
Fine-tuning of filter application in calculations.

Type of Calculations

This function performs:

Context preservation to ensure that new filters are additive.
Advanced filtering in scenarios requiring complex logic.

Practical Use Cases

Complex Filtering: Combine multiple filters in a query while keeping the initial filter context intact.
Measure Creation: Create measures that rely on additive filtering logic.
Advanced Reporting: Use in advanced visuals or reports requiring precise control over filter interactions.


KEEPFILTERS(<filterexpression>)</filterexpression>

Parameter	Type	Description
`FilterExpression`	Expression	A DAX filter expression to be applied. The function ensures it is additive.

How Does KEEPFILTERS Dax Works

The KEEPFILTERS function evaluates the existing filter context in the query.
It applies the specified FilterExpression without removing prior filters.
The result is a combination of the original filters and the new filter, yielding a refined dataset.

Logical Flow

Evaluate Context: Identify existing filters.
Apply Additional Filters: Add new filters via FilterExpression.
Combine Results: Preserve prior filters while applying new ones.

Example Formula

Sales with Filter =
CALCULATE(
SUM(Sales[Amount]),
KEEPFILTERS(Sales[Region] = "North")
)

This calculates the total sales in the “North” region without removing other existing filters.

What Does It Return?

Table: The function returns a table where the specified filter expression is applied in addition to the existing filters.

When Should We Use It?

Preserving Filters: When existing filters should not be overridden.
Dynamic Calculations: To dynamically combine multiple filtering criteria.
Advanced Visualizations: For complex visuals where filter precedence matters.

Examples

Basic Usage :


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

Applies a filter for “Electronics” while keeping any pre-existing filters intact.

Column Usage


Filtered Product Count =
CALCULATE(
COUNT(Sales[ProductID]),
KEEPFILTERS(Sales[Region] = "West")
)

Counts products in the “West” region without overriding other filters.

Advanced Usage


Dynamic Sales Calculation =
CALCULATE(
SUM(Sales[Amount]),
KEEPFILTERS(FILTER(Sales, Sales[Amount] &gt; 100))
)

Filters rows with sales amounts greater than 100 while preserving other filters.

Tips and Tricks

Combine with FILTER: Use with FILTER for complex row-level logic.
Be Cautious: Ensure KEEPFILTERS is used where additive filtering is intended; unintended results may occur in simple scenarios.
Performance: Test performance when applied to large datasets with complex filters.

Performance Impact of KEEPFILTERS DAX Function:

Optimal for Layering: Works best in measures or calculations requiring layered filtering.
Complex Queries: May slightly impact performance in large datasets with multiple filters.

Related Functions You Might Need

REMOVEFILTERS: Removes filters in a context.
ALL: Clears all filters, overriding the context.
CALCULATE: Often used with KEEPFILTERS to modify filter context.

Want to Learn More?
For more information, check out the official Microsoft documentation for KEEPFILTERS You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the primary purpose of KEEPFILTERS?

It ensures that new filters are applied without removing existing ones.

2. Can KEEPFILTERS be used without CALCULATE?

While technically possible, it is most effective when used within CALCULATE.

3. How does KEEPFILTERS differ from REMOVEFILTERS?

KEEPFILTERS adds to the existing filters, whereas REMOVEFILTERS clears them.

4. Is KEEPFILTERS useful for visual-level filters?

Yes, especially in scenarios where visual and measure filters need to coexist.

5. What happens if the filter expression conflicts with existing filters?

The new filter is additive and may refine or further limit the dataset.

Dax Function: INDEX

Dax Function: LASTNONBLANK