How to Use ALL in a DAX Expression in Power BI

Posted on by Reza Rad

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.

Why ALL?

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:

Now You See Me! Use cases of ALL DAX Function in Power BI

Sample Model

To understand the rest of the article, I start showing you the data model I am working with, which is as below:

Sample Report

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())

Other Variations

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:

    Enter Your Email to download the file (required)

    Video

    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    Reza Rad
    Trainer, Consultant, Mentor
    Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
    Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
    He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
    Reza’s passion is to help you find the best data solution, he is Data enthusiast.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

    Leave a Reply