DAX in Power BI provides various functions to make calculations easy. Two commonly used functions for removing filters are REMOVEFILTERS and ALL. While they appear similar, they have differences in their behavior and the performance measures. ALL in Power BI is used to remove filters from a table, keeping the relationship intact, while REMOVEFILTERS clears filters from the specified columns or tables. Unlike ALL, it does not return a table, but rather modifies the filter context. In this blog, you will explore REMOVEFILTERS and ALL in Power BI, understand their differences, and consider performance.
Table of Contents:
ALL Function in Power BI
The ALL function in Power BI is used to remove filters from a selected table or column and return all rows while ignoring any filters if applied. It is used to compute totals and percentages without its result being affected by slicers or filters. This function is useful for comparing filtered results against the entire dataset.
Syntax:
ALL(<table_or_column>)
REMOVEFILTERS Function in Power BI
The REMOVEFILTERS function in DAX explicitly removes filters from selected columns or tables. It is very similar to ALL functions but focuses mainly on clearing filters without affecting relationships. REMOVEFILTERS returns a table expression, but unlike ALL, it doesn’t override relationships.
Syntax:
REMOVEFILTERS(<table_or_column>)
Advantages of Using ALL and REMOVEFILTERS in Power BI
| ALL Function | REMOVEFILTERS Function |
| ALL Functions work well for ignoring filters in aggregation. | REMOVEFILTERS is more readable and remove filters explicitly. |
| ALL functions can be used in SUMX, CALCULATE, and other DAX expressions. | REMOVEFILTERS provides better control over the filters that need to be removed. |
| The ALL allows you to remove filters applied to all columns of a selected table. | REMOVEFILTERS works like ALL but does not override relationships as ALL does. |
| ALL is used to create unfiltered totals. | REMOVEFILTERS prevents unexpected changes to row content. |
| ALL is used for percentage-based calculations. | REMOVEFILTERS works better in a complex environment. |
Disadvantages of Using ALL and REMOVEFILTERS in Power BI
| ALL Function | REMOVEFILTERS Function |
| ALL Functions can alter the content present in a row. | REMOVEFILTERS is less used than ALL. |
| ALL functions are less explicit when working with multiple tables. | REMOVEFILTERS is not present in older versions of Power BI. |
| The ALL Function removes all filters from the specified table. | REMOVEFILTERS can remove multiple filters at once by passing them together. |
| ALL can affect the relationship between tables. | REMOVEFILTERS requires extra effort to apply the filter. |
| ALL can take more processing time when used in nested calculations. | REMOVEFILTERS does not work in every filtering scenario. |
Step-by-Step Examples for Understanding ALL vs REMOVEFILTERS in Power BI
We will create a dataset that we will be using to understand the working of both functions.
Example: Sales Data Example
| OrderID | Customer | Region | Product | SalesAmount |
| 1 | Alice | East | Laptop | 1000 |
| 2 | Bob | West | Phone | 500 |
| 3 | Alice | East | Tablet | 750 |
| 4 | Charlie | North | Laptop | 1200 |
| 5 | Bob | West | Tablet | 900 |
| 6 | Alice | East | Phone | 650 |
Step 1: Load the dataset
Open the Power BI desktop and load the dataset into Power BI.
Step 2: Apply filters in Power BI
We have applied a filter in the region column to display all regions of the East.
Total Sales of Region East when filter is applied = 1000 + 750 + 650 = 2400
Step 3: Using the ALL function
Write ALL function DAX formulas
Total Sales (Ignoring Filters) = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales))
Output:
This function is used to calculate the total sales across all regions and remove all the filters applied to the table.
Total Sales = 1000 + 500 + 750 +1200 + 900+ 650 = 5000
Step 4: Using the REMOVEFILTERS function
Total Sales (Ignoring Region Filter) = CALCULATE(SUM(Sales[Sales Amount]), REMOVEFILTERS(Sales[Region]))
Output:
This filter is used to remove the region filter, and after removing the filter, the total sales are
Total Sales = 1000 + 500 + 750 +1200 + 900+ 650 = 5000
Step 5: Comparing Output
| Function Used | Output | Filters Removed |
| Filtered Data | 2400 | No Filters Removed |
| ALL | 5000 | Removed All Filters |
| REMOVEFILTERS | 5000 | Remove Only Region Filter |
ALL Function vs REMOVEFILTERS Function in DAX
| Aspect | ALL Function | REMOVEFILTERS function |
| General Performance | When working with large datasets, the ALL Function may take additional processing time. | If multiple columns need filter removal, REMOVEFILTERS might need to be applied multiple times. |
| Efficiency | ALL functions can be less efficient when working with large datasets. | REMOVEFILTER works well only when it is selected or specifies the filter to be removed. |
| Filter Removal Scope | ALL Filter removes all filters applied on the selected column. | REMOVEFILTER explicitly removes the filter from the selected column, which can reduce unnecessary results. |
| Use Case in Large Datasets | ALL filters take time to process when working with large datasets. | REMOVEFILTER is suitable for large datasets when you need to remove filters from specific columns, which improves query performance. |
| Nested Calculations | ALL functions can affect performance when working with nested calculations. | REMOVEFILTER can work well with nested calculations. |
Real-World Example
A CFO wants to find out the amount of money spent by a department against the company’s total budget.
Step 1: Load the Data into Power BI
Click Home>Load Data>Load the data.
Step 2: Add Slicers
Add a Slicer for Department=IT and Year=2024. After adding the slicer, the visual will show
- Budget = 160000
- Actual = 150000
Company-Wide Budget (Ignore Department Filter using REMOVEFILTERS)
Total_Company_Budget = CALCULATE(SUM(FinanceData[Budget]), REMOVEFILTERS(FinanceData[Department]))
Output:
Limitations of ALL and REMOVEFILTERS Functions in Power BI
| Limitation |
ALL Function |
REMOVEFILTERS Function |
| Relationship Ignorance |
The ALL Function removes filters from a table, but it can break relationships when used inside CALCULATE. |
REMOVEFILTERS does not ignore relationships between tables while removing filters applied through a slicer. |
| Impact on Calculated Columns |
If used on a table, it can change the behaviour of the calculated column in aggregation. |
While it doesn’t directly affect calculated columns, REMOVEFILTERS may influence how those values appear in visuals depending on context. |
| Limited to Specific Scenarios |
It may not be the correct function to use in all cases. |
It may not be the right function to use in all cases. |
| Performance with Large Datasets |
ALL functions may reduce performance in larger datasets due to the removal of all filters. |
REMOVEFILTERS is more efficient when you are working with large datasets. |
| Use in Aggregation |
When the ALL Function is used in aggregation, it may lead to incorrect results. |
It is similar to ALL but has more efficiency while removing the specific filter. |
Best Practices
- If you want to remove filters from an entire table, use ALL.
- If you want to have more control over your dataset, then use REMOVEFILTERS.
- Always test performance with large sample datasets to see which function produces the best output.
- Use Function with KEEPFILTERS when it is important to maintain context.
- Always use comments while writing formulas; only then will the user be able to understand.
Conclusion
ALL and REMOVEFILTERS serve similar purposes but differ in execution and performance. ALL is used for broad filter removal and table-wide calculations. REMOVEFILTERS is used for a more controlled and explicit way of clearing filters. You can improve the DAX formula for better performance by understanding the behaviour and best methods. ALL is used to return entire tables or columns, which helps in comparing values. REMOVEFILTERS is used when you want to clear filters without affecting others in the calculation context.
To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.
REMOVEFILTERS vs ALL in Power BI – FAQs
Q1. When should I use ALL instead of REMOVEFILTERS?
You can use ALL when you want to remove all filters from the table.
Q2. When should I use REMOVEFILTERS instead of ALL?
You can use REMOVEFILTERS when you want to remove a specific filter and keep others as they are.
Q3. Does ALL affect the Filter context?
Yes, ALL affects filter context.
Q4. Can I use REMOVEFILTERS in SUMX?
Yes, you can use REMOVEFILTERS in SUMX to control which filters are removed.
Q5. Does ALL work with multiple columns?
Yes, ALL can work on multiple columns.