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 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:
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:
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.
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!!