The difference between ALL and ALLSELECTED DAX functions can be relatively confusing when you’re starting out with Power BI. In this tutorial, I’ll quickly go over the main differences between those two commonly used DAX functions and their respective usages in your Power BI reports. You may watch the full video of this tutorial at the bottom of this blog.
Let’s take a look at this example. I’ll use this to discuss the main difference between the ALL and ALLSELECTED DAX functions.
Analyzing The Visualization For The Highest Sale Month
In this example, I’ll be identifying the highest sale in any particular month. At the same time, I want to visually show the highest sale in this chart. As you can see here, I’ve already set up the chart which shows the revenue for every month. It also displays the chart lines that indicate the highest value in each of those unique months.
In this visualization, I got results from using both the ALL and ALLSELECTED DAX functions.
As I’ve said, there is a major difference between those two functions. The ALL function will identify the highest month from the beginning of time regardless of the context from the Date filter.
On the other hand, the ALLSELECTED function will consider the Date context on the report page.
Formula For The Highest Sale Month Measure
For this tutorial, I’ll be using the Highest Sale Month measure that I created as an example.
This formula is used to calculate the highest sale per month. Now, I’ll add this to our report as a card.
As you can see, it’s calculating the Highest Sale Month from the sample table even without any context applied to it. Furthermore, if you look closely into the data from the table, you’ll notice that the displayed value from the card visual is from the month of January in 2016.
Let’s now bring the Highest Sale Month measure into the sample table.
Interestingly enough, a certain iteration is happening within the VALUES function. Remember that when using this function, duplicated values are removed and only unique values are being returned. In this case, every single row of the Highest Sale Month column is being evaluated to every single month from the Month and Year column.
That means it’s only iterating through one particular month for each row. This is in contrast to what the formula for the Highest Sale Month card visual is doing.
Essentially, the formula for the Highest Sale Month card visual iterates through all of the months in a specified context.
However, what I was aiming to do here is to display the highest sale of a month with the same result all the way down to the last row of the Highest Sale Month column. In this case, I’m going to create a measure in which I can use the ALL function.
Using The ALL Function In DAX
Let’s take a look at the Highest Sale Month ALL measure that I created.
For this particular measure, I used the ALL DAX function.
This is a common way to use the ALL function. Most of the time, you have to use it in combination with the CALCULATE or CALCULATETABLE function. There are also times when you need to place it inside a filter like the FILTER function.
As you can see, this part of the formula is exactly the same with the previously discussed Highest Sale Month measure.
On the other hand, I want to calculate it in a different context. Thus, I’ll change the context of the calculation by using the CALCULATE function.
As you can see, the result from the Highest Sale Month ALL column is different from the displayed result within the Highest Sale Month card visual.
I created another card which will display the result of the Highest Sale Month ALL measure. This is just to clearly show the difference between the two results.
The result within the Highest Sale Month ALL measure is different because it is not considering the context that I have selected within the Date filter. It’s actually looking at every month through time.
In this case, if I extend the timeframe within the Date filter, you’ll see that both results will be the same.
But if I set a shorter timeframe, the highest amount in this particular date selection will become different. This is because of the ALL function which removes filters on all dates.
Using The ALLSELECTED Function In DAX
In line with the previously discussed example, I need to use the ALLSELECTED DAX function. This is to remove filters specifically on the dates within the current context that I have selected in the report page.
The ALLSELECTED function removes any filter just like what the ALL function does. The only difference is that it still considers the overarching context in a report page.
Most probably, you’ve already seen how the ALLSELECTED function works in a cumulative total pattern calculation. That’s somehow similar to this because in calculating a cumulative total, there’s also a date selection in the report page.
You won’t really care about calculating the cumulative total from the beginning of time. In most cases, you only want to see the results based on the context that you have selected within the Date filter. Then, you’d want it to be a dynamic calculation that updates every time you make a selection within the Date filter.
***** Related Links*****
ALL Function in Power BI – How To Use It With DAX
A Deep Dive Into How The ALLSELECTED DAX Function Is Used In Power BI
Using The ALL Function In Power BI For High Quality Insights
Conclusion
To summarize, the ALL function calculates all the values in a column regardless of the applied context filter. The ALLSELECTED function also returns all the values in a column, but it removes context filters from columns and rows in the current query while keeping the filters that come from outside.
Now, that’s the main difference between those two DAX functions that you need to understand.
It’s a subtle distinction, but it can make a big difference in the calculation that you get in a certain context.
It is essential to thoroughly understand what is causing those differences especially if you’re going from the ALL function to the ALLSELECTED function.
All the best,
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
