Elevate DAX: ALLSELECTED & ALLCROSSFILTERED Demystified
In this Part 2 of ALLxxx Family of Functions in Power BI, Focus is on ALLSELECTED AND ALLCROSSFILTERED
Hello Readers! Welcome to Part 2 of the blog on ALLxx family of functions.
Thank you so much for liking the Part 1, where I explained ALL, ALLEXCEPT and ALLNOBLANKROW and If you haven’t had a chance to read that yet, you can read from 👇.
In this part, the focus is on ALLSELECTED and ALLCROSSFILTERED. Both these DAX functions are unique in their own way. I’ll be explaining how they can be instrumental in certain scenarios.
So, without further ado, let’s just start!
ALLSELECTED
If you know how ALL works, then understanding ALLSELECTED will be easy. Like ALL, ALLSELCTED can work both as a Table function and as a Calculated Modifier.
While these two DAX functions operate similarly, there’s a fundamental difference. ALL is a Filter remover i.e it ignores any filters irrespective of where they come from. In contrast, ALLSLECTED ignores only those filters that come from the Visual itself.
This means that if a filter comes from another visual, a slicer, or the filter pane itself, ALLSELECTED will take that into consideration while performing the calculation.
Take a look at the code snippet and table visual below, with ALLSELCTED calculation.
In the visual, I’ve included the results of Profit calculations using both ALL and ALLSELECTED. I’ve also added slicers for the Segment and Product fields.
The first observation is, we are getting the same result for Profit using ALL(financials[Product]) and ALLSELCTED(financials[Product]). Profit is being calculated at the Segment level, ignoring filters on the individual Products in the table visual.
Now, let’s see what happens when we select only specific Products from the slicer.
As you can see, we selected only the first four products from the Product Slicer instead of all the products.
In the result, it’s clear that the [Profit AllSelected] measure shows data as per the slicer selection, whereas the [Profit All] measure still ignores it. [Profit AllSelected] displays the combined profit for only the four selected products, namely Amarilla, Carretera, Montana, and Paseo.
Also, it’s worth noting that ALLSELECTED can definitely be used on multiple columns or on a table itself.
Business Use Case — ALLSELECTED
There are various scenarios where ALLSELECTED can prove useful. One such use case arises when a business user wants the Profit % for each product to be calculated based on the current filter context.
Typically, the Profit % for a product is calculated by dividing the individual profit value by the total profit value.
But in this scenario, the total profit is to be calculated based on the products selected from the slicer? Let’s see how this can be done. I’ll first try to calculate that using ALL.
Upon close inspection, you’ll notice that the result is not as expected.
Firstly, the Profit % calculated at the Segment level is incorrect. It should be 100% because, for each segment, the profit would be equal to the total profit of all the products in the current filter context.
Secondly, the Total Profit% at the Product level should consider only the selected products in the slicer. However, due to the presence of ALL, all the products are considered.
So, how do we fix this? Simply, we need to use ALLSELECTED instead of ALL in such scenarios. Check below.
Now, we can clearly see that the results are correct and meet our expectations. It has overcome both the issues we encountered using ALL.
Here, the profit for the individual product shown in the table visual is divided by the total profit based on the filter from the product slicer, which is exactly what we wanted.
This wraps up ALLSELECTED. There is still more to be discussed about ALLSELECTED, but that will be covered in another blog post.
ALLCROSSFILTERED
ALLCROSSFILTERED works quite similarly to ALL, but there are two basic differences:
ALLCROSFILTERED supports cross-filtering on tables, while ALL is limited to expanded tables.
Unlike ALL, which is used both to return a table and as a calculate modifier, ALLCROSSFILTERED is only used as a calculate modifier.
Let me explain these two differences with an example.
Consider the data model screenshot below, where our Product table is connected to the Financials table in a one-to-many relationship. As you can see, I have also set the Cross Filter direction to Both.
Now, let’s create a measure to calculate Profit using ALL by removing any filter from the Product table.
See the code snippet and table visual with the result below.
As you can clearly see, the [Profit ALL] measure is not giving us the correct result. This is because ALL does not work with cross filters as mentioned earlier. To get the result that we want, we need to use ALLCROSSFILTERED instead in such cases. See below:
We are now getting the Total profit across all the products correctly, which is what we expected.
In essence, remember to use ALLCROSSFILTERED instead of ALL in the following two scenarios:
- When you want to remove filters from the cross-filtered tables.
- When dealing with Weak or Limited relationships.
Although I have not covered the second scenario, both scenarios ultimately refer to the same thing. If you want me to cover the second scenario as well, please let me know in the comments. I will definitely take that up in another blog post.
Conclusion
This concludes our two-part blog series on the ALLxxx family of functions. In this series, I have attempted to explain in detail the functioning of ALL, ALLEXCEPT, ALLNOBLANKROW, ALLSELECTED and ALLCROSSFILTERED DAX functions.
I want to reiterate that if you aim to understand DAX better, a solid grasp of the functioning of the ALLxx family of functions is crucial. If the understanding around these functions is not clear, confusion may arise about which function to use and where to use it.
If you got any questions or need more clarity? Please drop a comment. Your feedback is also invaluable. I’ll soon be back with another piece. Until then, keep learning Power BI and DAX!
Before you go, just wanted say I am truly grateful that you have chosen to be part of this journey. All my blogs are open to all the readers on medium whether you are a member or not.
Considering the time and effort i put in writing these blogs, I have started a Buy Me A Coffee page. You can support my writing journey by generously contributing. I would be deeply appreciative.
You can do that by clicking the image below 👇.
Consider connecting with me through my social media handles; I look forward to hearing from you!
Don’t forget to subscribe to
and join our Power BI community
