DAX Tutorial: Keepfilters Function in Power BI.

The KEEPFILTERS function allows you to modify this behavior. When you use KEEPFILTERS, any existing filters in the current context are compared with the columns in the filter arguments, and the intersection of those arguments is used as the context for evaluating the expression. In this article, we will explore what the Keepfilters function does and provide examples of how it can be used in Power BI.

What is the Keepfilters Function?

The Keepfilters function in DAX is used to preserve existing filters on a table or column while applying new filters. It allows you to specify which filters you want to keep and which ones you want to replace. By using this function, you can ensure that certain filters remain in place while applying additional filters to further refine your data.

Syntax

The syntax for the Keepfilters function is as follows:

KEEPFILTERS(<expression>)

The <expression> can be any valid DAX expression that returns a table or a column.

Examples

Let’s take a look at some examples to better understand how the Keepfilters function works.

Example 1: Keeping Filters on a Specific Column

Suppose we have a sales dataset that includes columns for “Product,” “Region,” and “Sales.” We want to analyze the sales data for a specific region while keeping the filters on the “Product” column intact.

CalculatedTable =
KEEPFILTERS(SUMMARIZE('Sales', 'Sales'[Region], "Total Sales", SUM('Sales'[Sales])))

In this example, the Keepfilters function is used to create a new calculated table called “CalculatedTable.” The function preserves the existing filters on the “Product” column while summarizing the sales data based on the “Region” column.

Example 2: Combining Multiple Filters

Let’s say we have a dataset that includes columns for “Product,” “Category,” and “Sales.” We want to analyze the sales data for a specific category and region while keeping the filters on the “Product” column intact.

CalculatedTable =
KEEPFILTERS(
    FILTER(
        SUMMARIZE('Sales', 'Sales'[Region], 'Sales'[Category], "Total Sales", SUM('Sales'[Sales])),
        'Sales'[Category] = "Electronics" && 'Sales'[Region] = "North"
    )
)

In this example, the Keepfilters function is used in combination with the Filter function to create a new calculated table called “CalculatedTable.” The Keepfilters function ensures that the filters on the “Product” column are preserved, while the Filter function applies additional filters on the “Category” and “Region” columns.

Example 3: Using Keepfilters with Related Tables

Suppose we have two tables: “Sales” and “Products.” The “Sales” table includes columns for “ProductID,” “Region,” and “Sales,” while the “Products” table includes columns for “ProductID” and “Category.” We want to analyze the sales data for a specific category while keeping the filters on the “Product” column intact.

CalculatedTable =
KEEPFILTERS(
    SUMMARIZE(
        ADDCOLUMNS(
            'Sales',
            "Category", RELATED('Products'[Category])
        ),
        'Sales'[Region], 'Sales'[Category], "Total Sales", SUM('Sales'[Sales])
    )
)

In this example, the Keepfilters function is used in combination with the Summarize and Addcolumns functions to create a new calculated table called “CalculatedTable.” The Keepfilters function ensures that the filters on the “Product” column are preserved, while the Related function is used to retrieve the “Category” information from the “Products” table.

Conclusion

The Keepfilters function in DAX is a powerful tool that allows you to preserve existing filters while applying new filters in Power BI. By using this function, you can easily manipulate and analyze your data without losing important filtering criteria. Understanding how to use the Keepfilters function will enhance your data analysis capabilities in Power BI and help you gain valuable insights from your data.