How to Use ALL in a DAX Expression in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

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.

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;

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;

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.

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply