In Power BI DAX, both KEEPFILTERS and REMOVEFILTERS are functions used to manipulate filters within calculations. Let’s delve into each with an example:
1. KEEPFILTERS:
The KEEPFILTERS function is used to preserve existing filters while applying new filters within a calculation. It ensures that only the specified filters are applied, keeping all other filters intact.
Syntax:
DAX
KEEPFILTERS(<expression>)
Example: Suppose you have a Sales table with columns for SalesAmount, ProductCategory, and Date. You want to calculate the total sales amount for the “Electronics” category while keeping any existing filters on the Date column.
DAX
Total Sales (Electronics) =
CALCULATE(
SUM(Sales[SalesAmount]),
KEEPFILTERS(Sales[ProductCategory] = "Electronics")
)In this example, the CALCULATE function calculates the total sales amount for the “Electronics” category. However, the KEEPFILTERS function ensures that any filters applied to the Date column outside of this calculation are preserved.
To use in Sample Superstore Data :
Total Sales (South) KF =
CALCULATE(
SUM(Orders[Sales]),
KEEPFILTERS(Orders[Region] = "South")
)2. REMOVEFILTERS:
The REMOVEFILTERS function is used to remove all filters from the specified columns within a calculation, providing a way to ignore any existing filters temporarily.
Syntax:
DAX
REMOVEFILTERS([<columns>], <expression>)
Example: Consider you want to calculate the total sales amount for all product categories, ignoring any filters applied to the Date column.
DAX
Total Sales (All Categories) =
CALCULATE(
SUM(Sales[SalesAmount]),
REMOVEFILTERS(Sales)
)Here, the CALCULATE function calculates the total sales amount for all product categories. However, the REMOVEFILTERS function ensures that any filters applied to the Date column are temporarily removed during this calculation.
These functions are useful for controlling the scope of filters within DAX calculations, allowing you to achieve precise and flexible analysis in Power BI.
Example Dataset for Keepfilters and Removefilters
Let’s create example data for a simplified scenario where we have a Sales table with columns for SalesAmount, ProductCategory, and Date.
Here’s how the example data might look:
| SalesAmount | ProductCategory | Date |
|---|---|---|
| 1000 | Electronics | 2024-01-01 |
| 1500 | Clothing | 2024-01-05 |
| 2000 | Electronics | 2024-01-10 |
| 800 | Electronics | 2024-01-15 |
| 1200 | Clothing | 2024-01-20 |
| 3000 | Electronics | 2024-01-25 |
With this data, we can demonstrate how KEEPFILTERS and REMOVEFILTERS work.
Example using KEEPFILTERS:
Suppose we want to calculate the total sales amount for the “Electronics” category while keeping any existing filters on the Date column.
DAX
Total Sales (Electronics) =
CALCULATE(
SUM(Sales[SalesAmount]),
KEEPFILTERS(Sales[ProductCategory] = "Electronics")
)Example using REMOVEFILTERS:
Suppose we want to calculate the total sales amount for all product categories, ignoring any filters applied to the Date column.
DAX
Total Sales (All Categories) =
CALCULATE(
SUM(Sales[SalesAmount]),
REMOVEFILTERS(Sales)
)These examples showcase how KEEPFILTERS and REMOVEFILTERS can be used in DAX calculations to manipulate filters and perform calculations based on specific criteria.
