Using ALL() to Ignore Filters
Using ALL to Ignore Filters in Power BI
Using ALL to Ignore Filters, the ALL() function returns all the data in a table or all the values in a column while ignoring any filters that might apply. Essentially, it clears the filters from the specified table or column.
ALL() has a special feature that differentiates it from all other functions. ALL() ignores the filter context. The result of ALL() depends on the input argument: When you pass the table as an argument, ALL() returns all the rows of the table (including duplicate rows) while ignoring any filter context. You can use these values with an X-function or iterators. When you pass the column as an argument, ALL() returns all the unique values.
ALL() using single column:
To see how ALL() is used to create tables:
- Click on the Modeling View tab in the Ribbon.
- Click on New Table.
Creating a new calculated table in Power BI Desktop Using ALL to Ignore Filters
- Type the following expression in the formula bar and press Enter.
Figure 3: Power BI DAX code for retrieving distinct categories from Product Table
In this Expression:
- The unique product categories from the Product table are found because the argument to ALL() is s table column
- Click on the Table View tab in the left Ribbon.
- Click on All Categories in the Fields pan.
Figure 4: Result of DAX code to calculate distinct categories from Product table in Power BI
ALL() using multiple columns:
To understand the behavior of ALL() using multiple columns:
- Click on the Modeling tab in the Ribbon.
- Click on New Table.
- Type the following expression in the formula bar and press Enter.
Figure 5: Power BI DAX code for retrieving distinct combinations of category and subcategories
In this Expression:
- Two columns from the Product table are used as arguments in the ALL() function.
- Click on the Table View tab in the left Ribbon.
- Click All Category-SubCategory Combinations in the Fields pan.
Distinct combinations of category and subcategory returned after DAX calculations in Power BI Using ALL to Ignore Filters
Notice that distinct combinations of category and subcategory are returned. Let’s see what happens when two columns from different data sets are used as arguments in ALL().
- Click on the Modeling tab in the Ribbon.
- Click on New Table.
- Type the following expression in the formula bar.
Figure 7: Error message displayed when trying to fetch columns from different tables using ALL() in Power BI
You can’t select two columns from different tables. Now let’s see what happens when a table and column (of a different table) are used as arguments in ALL().
- Click on the Modeling tab in the Ribbon.
- Click on New Table.
- Type the following expression in the formula bar.
Figure 8: Error message displayed when trying to use a table and column from different tables in Power BI
The warning shows that this type of syntax combination is not allowed in ALL().
This can be done using SUMMARIZE(). Which will be explained in the upcoming blogs.
Downloads
FAQs on Using ALL() to Ignore Filters
In Power BI, you can use the “ALL()” function to ignore all filters in a specified table. You can apply the “ALL()” function to a specific table or a column, and it removes any filters or context applied to that table or column. This allows you to create calculations that disregard any active filters. By using “ALL,” you can ensure that your calculations remain unaffected by any filtering conditions.
ALL() removes all filters from the specified table or column, while ALLSELECTED() retains the user’s filters but clears filters from other tables or columns. This action allows you to control the context more precisely within DAX calculations in Power BI. Your choice between them depends on your specific analytical needs and how you want your calculations to interact with user-driven filtering.
By using the ALL() function within the CALCULATE() function, you create a measure that effectively ignores filter selections and provides a consistent view of the data regardless of user interactions.