# The Difference Between ALL And ALLSELECTED DAX Functions In Power BI

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.

## 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 – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Power Query: How To Merge Tables W/Different Columns

In this post, I'm going to show you how to do a conditional merge of tables in power query. For this...

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Dashboard Design – An Impressive Page Turning Visualization Idea

Power BI Dashboard Examples In this tutorial, I want to highlight one of the most innovative...

## Power BI Visual – Showcase Customer Purchase Dates

In this blog, I’ll share a great Power BI visual tip. I'll show you how to dynamically visualize things...

## Power BI DAX Measures For Events In Progress

In this tutorial, I'm going to show you how to create Power BI DAX measures for events in progress....

## Small Multiples Chart In Power BI: An Overview

In this tutorial, we’ll talk about the small multiples chart, which is a new preview feature introduced...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX Measure Analysis: Breaking Down Long DAX Measures

In this tutorial, I'm going to show you some detailed DAX measure analysis on how I put together one of...

## Business Themed Power BI Dashboard – Power BI Online Service

This is a quick tutorial about how you can make your Power BI dashboard and app more presentable and...

## Understanding Evaluation Context in Power BI

The most important concept in understanding DAX is context. There are three main types of context: the...