I have previously written a blog post about what the ALL function in Power BI is, and how it helps in working with filters in your report. However, still I get many students confused about how to use this function, which parameter to pass to it. This blog post is explaining that in detail.
ALL is a very useful function in DAX that ignores the filters. Because in Power BI, measures are always affected by the filters coming through visuals (filter context), the ability to sometimes IGNORE these filters, can be very useful on many occasions.
For example, you can use ALL in an expression like below to get the total SalesAmount regardless of filters applied in other visuals or slicers;
To understand how ALL works in details, I highly recommend reading my article here:
To understand the rest of the article, I start showing you the data model I am working with, which is as below:
I also have the below report as a sample:
In the above screenshot, you can see that the Sales measure’s value is affected by three filters: Color from DimProduct, EnglishPromotionName from DimPromotion, and EnglishEducation from DimCustomer. Although they are not defined as a filter, two of them are slicers, and one is a column in the table visual, still, they are filtering the values calculated by the measure.
ALL Ignores the Filter Coming from One Table
There are many variations that you can use the ALL function. one is to ignore filters coming from only one of the tables:
Sales All Customers = CALCULATE([Sales],ALL(DimCustomer))
The above expression ignores the filters coming from EnglishEduction (which is a column from DimCustomer) but accepts the filters from the other two tables. As a result, this measure would return the total sales for all customers that can be still sliced and diced by other tables.
ALL can be used to get the total and calculate the percentage based on that.
If you change the input table of ALL expression to ALL(DimProduct) for example, then it will get filtered by the DimCustomer, and DimPromotion, but not by DimProduct. in other words; use the table that you want to calculate the total of it in ALL as the input.
Sales All Products = CALCULATE([Sales],ALL(DimProduct))
The calculation above gets filtered by the Promotion, and by the Education, but not by the Color (from DimProduct).
Ignoring Filters from Two Tables
You can use ALL to ignore the filters coming from more than one table. One way to achieve this is to use multiple ALL inside the calculate expression like below;
Sales All Customers and Products = CALCULATE( [Sales], ALL(DimCustomer), ALL(DimProduct) )
The expression below won’t accept any filters coming from the DimCustomer or DimProduct tables.
Ignoring filters from All tables
In some scenarios that you want to calculate the grand total regardless of the selection of any filters or slicers, then you need to ignore filters coming from all the tables, there are two common approaches for that;
Using All for the Fact Table
If you use the Fact table inside the ALL expression, then it will ignore all the filters coming from other tables. The fact table would be the table that your value is coming from;
an expression such as below will give us that result;
Sales All Fact Table = CALCULATE( [Sales],ALL(FactInternetSales) )
the expression above ignores filters coming from the FactInternetSales itself, which then means ignoring filters coming from all the dimensions around it.
Using ALL without Input Parameter: Ignore Everything
Another approach is to use ALL without any input table or parameters, just as ALL(). This will ignore everything, which can be a good option if your calculation’s value comes from multiple tables and you don’t want any filters to effect it.
Sales All = CALCULATE([Sales],ALL())
There are other variations of using ALL, such as ALLExcept, or using ALL with other functions which can be helpful in ignoring some filters, but then accepting custom filters. I’ll explain those in other articles later.
Download Sample Power BI File
Download the sample Power BI report here: