ALL vs ALLSELECTED in DAX and Power BI Desktop

Hey all! In my latest YouTube video  on DAX I discuss how ALLSELECTED can be leveraged in Data Analysis Expressions (DAX). This continues a series of YouTube videos in which I discuss various DAX functions and how to use them in Power BI Desktop.

Please feel free to check out my YouTube channel and subscribe Winking smile at https://www.YouTube.com/c/MitchellPearson

If you want to watch the video of this function explained, please take a look below:

ALLSELECTED function in DAX and Power BI Desktop

First, let’s look at the definition of ALLSELECTED, provided inside Power BI desktop when referencing the function:

image

ALLSELECTED: Returns all the rows in a table, or all the values in a column, ignoring any filters that may have been applied inside the query, but keeping filters that come from the outside.

What does this mean? Well, it means that it will essentially ignore filters coming from that visual but still respect filters coming from slicers and filters outside that visual (the outer query)! And yes, this is pretty awesome as you will soon see.

ALL vs. ALLSELECTED DAX functions

The biggest difference between ALL and ALLSELECTED is that the ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

Returning Total Sales of all Selected Countries

The following expression in DAX would return the total sales for all countries in all scenarios. For example, the countries Australia, Canada and France were selected in the slicer, but yet the measure Total Sales All Countries still returns the total sales across all countries:

image

Figure 1.1

In this scenario, you might want to return Total Sales for all countries selected in the slicer (allselected), $13,682,863.16. This can be quite tricky, especially if you didn’t know of the existence of the ALLSELECTED function. Let’s write the expression in Figure 1.1 a different way and look at the results.
image

Figure 1.2

In this example you can clearly observe that ALLSELECTED is providing different way to analyze your data. I love this dynamic approach to analyzing data that is provided by simply modifying the ALL function to use ALLSELECTED!

As always, I hope this blog has helped someone somewhere and thanks for reading my blog!!

2 thoughts on “ALL vs ALLSELECTED in DAX and Power BI Desktop

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s