What's the Main Difference Between ALL & ALLSELECT...

EnterpriseDNA · ‎09-01-2020

When you get deeper into DAX, try out some DAX combinations to complete an analysis. You might encounter some confusion or difficulties with some of the DAX functions, and this is completely understandable, as some of the DAX functions are quite similar. One example of this is the ALL and ALLSELECTED.

I’ve seen many Power BI beginners get confused as to when and how to use these two commonly used DAX functions. The ALL and ALLSELECTED functions can be confusing when you’re just starting out with Power BI. So, I’ll quickly go over the difference between these two functions and their respective usages in Power BI reports.

There’s a lot to learn from this article and you can start off by watching this tutorial where I go over the main differences between the functions.

In this example, I’m trying to identify the highest sale in any month. In this demonstration, you’ll learn when to use ALL and ALLSELECTED.

In the visualization, I have results from using both the ALL and ALLSELECTED DAX functions.

2020-09-02 (2).png

The ALL function identifies the highest month from the beginning of time, regardless of the context from the Date filter. In other words, the ALL function removes filters from all dates. It can remove any context that is coming from any filter or dimension that you’ve aligned to a formula.

Use ALL when you need to remove or adjust the context of a calculation that you want to perform. ALL is usually used in combination with the CALCULATE function and sometimes with the FILTER function.

Here’s a tutorial that focuses on the ALL function. Watch it to have a clearer view on how this function is used.

In this tutorial, I’m calculating the percentage of sales in a region versus all the sales ever made. You see how the ALL function gets the results that I need.

The ALLSELECTED function, on the other hand, is one of the more advanced functions that you can utilize within your Power BI reports. It also removes any filter, the same with the ALL function. But, ALLSELECTED removes filters specifically on the dates within the current context that you select from the report page.

To understand this concept better, watch this tutorial I made about ALLSELECTED and how to use it.

In this tutorial, you see that I’m using ALLSELECTED to filter a specific timeframe (Cumulative Sales). I also show what the results look like when I change ALLSELECTED to ALL (Cumulative Sales ALL).

2020-09-02 (3).png

Context is the key here. You must have a good understanding of what context is to understand ALLSELECTED. The ALLSELECTED function removes context within a certain date selection, while the ALL function removes all context regardless of the dates being selected.

Key Take Away

To wrap it up, the ALL function looks at the complete set of data from beginning to end and calculates all values in a column, disregarding any filter or selection you make in your report. The ALLSELECTED function returns all the values in a column as well, but it removes context filters from columns and rows in the current query while keeping the filters that come from outside.

This is the main difference between the two functions. It’s quite easy to think about, but it can make a huge difference in the calculation and results that you get in a certain context.

For more examples on when and how to use these functions, check out the links below and visit our website Enterprise DNA for more educational content.

Cheers!

Sam

***** Related Links *****

Dynamically Calculate A Power BI Running Total Or Cumulative Total

Dynamically Calculating The Previous Highest Result Using DAX Functions

Using The ALL Function In Power BI For High Quality Insights

***** Related Course Modules*****

DAX Formula Deep Dives

Mastering DAX Calculations

Advanced DAX Combinations

***** Related Support Forum Posts *****

Problem With ALLSELECTED Context