DAX KEEPFILTERS(): Improve Accuracy in Power BI Measures
Table of Contents
Introduction
It is essential to understand filter context in DAX to create useful measures in Power BI. Functions such as CALCULATE(), FILTER(), and ALL() are widely used to control and manipulate this context. In a related guide, Mixing Table Functions: SUMMARIZE() & FILTER(), you can explore how combining table functions helps generate powerful insights by creating virtual tables and defining data conditions. In this article, we will learn how table functions, specifically KEEPFILTERS(), can be used to create more powerful insights.
You can download the Power BI file using the link provided below the blog post and practice this step by step.
In this article, we learn how to use table functions, specifically KEEPFILTER(), to create more powerful insights.
How filters are applied using KEEPFILTERS()
Start by creating a matrix visual:
- Add Country(from the Sales Territory table) to the Rows.
- Add Total Sales Amount (CM)and Sales in Australia (CM) to the Values.
Notice that the same value is shown for all the countries and this value basically is the sales amount for Australia only. For all countries other than Australia, the initial filter context conflicts with CALCULATE(). CALCULATE() essentially overrides the incoming filter context. The reason for this behavior is that Sales in Australia (CM) is executed internally as below:
In the above expression:
- ALL() ignores the filter coming from Country Column.
- FILTER() filters the Sales from “Australia” only and stores in a virtual table.
- The total sales amount for the rows in the virtual table is calculated.
Intersect:
Now, you can stop CALCULATE() from overriding the initial filter context by using KEEPFILTERS().
To see how KEEPFILTERS() work:
3. Right click on All Measures in the Fields.
4. Then, click on New measure.
Measure tools open.
5. Here, type the following formula in the formula bar and press Enter.
6. Click on the “$” in the formatting section.
7. Type “2” in the text box.
8. Drag and drop Sales in Australia using KEEPFILTERS() from the All Measures table to the Values.
Notice the change in behavior of CALCULATE(). Sales in Australia using KEEPFILTERS() shows values only for cells where the incoming filter context is not in conflict with that of CALCULATE().
Conclusion
Using KEEPFILTERS() changes how CALCULATE() interacts with the existing filter context by enforcing an intersection instead of an override. This results in more precise and context-aware calculations, especially when working with visuals like matrices where multiple filters are applied simultaneously. By incorporating KEEPFILTERS(), you can avoid misleading results and ensure that your measures respect both the user’s selections and your defined conditions.
FAQs
KEEPFILTERS() ensures that filters inside CALCULATE() do not override the existing filter context. Instead, it intersects both filters, leading to more accurate results.
By default, CALCULATE() replaces any existing filters on the same column if there is a conflict. This is why you may see the same value repeated across different categories in a visual.
No. It is only needed when you want to avoid filter replacement behaviour in CALCULATE(). In many cases, the default behaviour is sufficient.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enroll in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
Downloads
