Introduction
In Power BI, data analysis is not only about calculating totals, averages, or percentages. A large part of building meaningful reports involves controlling which data should be included in a calculation. This is where DAX filter functions play an important role.
Every visual, slicer, page filter, and report filter in Power BI creates a filter context. DAX functions can use, modify, remove, or create additional filters to perform calculations based on specific business requirements.
For example, a business may want to calculate sales only for a particular product category, compare current sales with total company sales, ignore a customer filter, or calculate revenue for a selected group of products. These scenarios require more than a simple SUM() function.
DAX provides several powerful functions for handling filters, including CALCULATE(), FILTER(), ALL(), ALLEXCEPT(), ALLSELECTED(), REMOVEFILTERS(), and KEEPFILTERS().
Understanding when and how to use these functions is essential for building flexible and accurate Power BI reports. In this article, we will explore some of the most commonly used DAX filter functions and understand them through practical business scenarios.
1. Using CALCULATE() to Modify Filter Context
CALCULATE() is one of the most important functions in DAX. It evaluates an expression while applying or modifying filters.
For example, suppose the business wants to calculate Sales only for the Electronics category.
Electronics Sales =
CALCULATE(
SUM(Sales[Amount]),
Product[Category] = “Electronics”
)
The base calculation is the total Sales Amount. However, CALCULATE() changes the filter context and includes only records where the Product Category is Electronics.
CALCULATE() is commonly used when creating measures that require a specific condition, such as Sales for a particular region, Revenue for a selected product group, or Actual Sales for a specific year.
In simple terms, CALCULATE() allows us to answer the question:
“What should this calculation return if I change the current filter context?”
2. Using FILTER() for Custom Filtering Logic
FILTER() returns a table containing only the rows that meet a specific condition.
Unlike a simple filter condition, FILTER() is useful when the filtering requirement is more complex.
For example, suppose the business wants to calculate sales only for transactions greater than $10,000.
Large Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Amount] > 10000
)
)
In this example, FILTER() evaluates each row in the Sales table and returns only the transactions where the Amount is greater than 10,000.
FILTER() is commonly used when calculations require multiple conditions or more advanced business logic.
Because FILTER() evaluates rows individually, it should be used carefully on very large datasets, especially when a simpler filter expression can achieve the same result.
3. Using ALL() to Remove Filters
The ALL() function removes filters from a table or column.
This is particularly useful when comparing a filtered value with the overall total.
For example, suppose a report displays Sales by Product Category, and the business wants to calculate each category’s percentage of total Sales.
Sales % of Total =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(
SUM(Sales[Amount]),
ALL(Product)
)
)
The numerator calculates Sales based on the current filter context. The denominator uses ALL(Product) to remove the Product filters and calculate total Sales across all products.
This allows the report to show how much each category contributes to the overall business.
4. Using ALLEXCEPT() to Keep Specific Filters
Sometimes we want to remove most filters but keep one or more specific filters. This is where ALLEXCEPT() can be useful.
For example, suppose the business wants to calculate total Sales for each Customer while ignoring all other filters from the Customer table.
Customer Total Sales =
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(
Customer,
Customer[CustomerID]
)
)
ALLEXCEPT() removes filters from the Customer table except for CustomerID.
This can be useful when building calculations where one business dimension should remain fixed while other filters are ignored.
5. Using ALLSELECTED() to Respect User Selections
ALLSELECTED() is useful when calculations need to respect the user’s overall selections while removing filters created by the current visual.
For example, imagine a user selects the years 2025 and 2026 using a slicer. A chart then displays Sales by Product Category.
The business wants each category to be compared with the total Sales for the selected years.
Selected Total Sales =
CALCULATE(
SUM(Sales[Amount]),
ALLSELECTED(Product)
)
ALLSELECTED() allows the measure to respect the broader report and slicer selections while adjusting the filter context created by the visual.
6. Using REMOVEFILTERS() to Clear Filters
REMOVEFILTERS() is another function used to remove filters from a table or column.
For example, the business may want to calculate total company Sales regardless of the currently selected Customer.
Total Company Sales =
CALCULATE(
SUM(Sales[Amount]),
REMOVEFILTERS(Customer)
)
Even if a specific Customer is selected in the report, this measure removes the Customer filter and returns Sales across all customers.
REMOVEFILTERS() makes the business requirement clear: the calculation should ignore filters from the specified table or column.
7. Using KEEPFILTERS() to Preserve Existing Filters
KEEPFILTERS() changes how filters are applied inside CALCULATE().
Normally, when CALCULATE() applies a filter to a column that is already filtered, the new filter can replace the existing filter. KEEPFILTERS() allows the existing filter to be preserved and combines it with the new condition.
For example:
Filtered Sales =
CALCULATE(
SUM(Sales[Amount]),
KEEPFILTERS(
Product[Category] = “Electronics”
)
)
If the report already contains filters, KEEPFILTERS() helps ensure that the calculation respects the existing context and applies the additional condition without unnecessarily replacing it.
This is useful when creating measures that need to add conditions while preserving the user’s existing selections.
Conclusion
Filter functions are a fundamental part of DAX and Power BI reporting. They allow developers to control how calculations respond to slicers, visuals, report filters, and business conditions.
CALCULATE() is used to modify filter context and is the foundation of many advanced DAX calculations. FILTER() provides flexibility for custom row-level filtering. ALL() and REMOVEFILTERS() help remove filters when calculating totals or benchmarks. ALLEXCEPT() allows specific filters to remain while others are removed. ALLSELECTED() is useful for calculations that need to respect the user’s overall selections, while KEEPFILTERS() helps preserve existing filters when additional conditions are applied.
The most important part of working with DAX filter functions is understanding filter context. Two formulas may look similar, but they can return very different results depending on which filters are active and how the DAX function modifies them.
By understanding when to use each filter function, Power BI developers can create more dynamic, accurate, and interactive reports that meet complex business requirements.
Author By
