Elevate DAX: 5 ALLxxx Functions That Confuse — Part 1
Understand In Detail About ALL, ALLEXCEPT, ALLSELECTED, ALLNOBLANKROW, ALLCROSSFILTERED Functions In Power BI
Greetings, dear readers! Welcome back to another exciting blog in the Elevate DAX series. This time, the focus is on the ALL family of functions.
If you are curious about my previous blogs in this series? You can catch up here.
This is a two-part blog. In this first part, I explain ALL, ALLEXCEPT, and ALLNOBLANKROW.
Introduction
Anyone with even basic DAX experience would have encountered these functions. For those transitioning from SQL, understanding data analysis using DAX can be challenging.
There are scenarios where a calculation that’s a easy in SQL becomes a complex puzzle in DAX if not written and understood correctly.
Many such scenarios require a solid understanding of ALLxxx DAX functions.
The ALL family of functions essentially serve two purposes:
As Table functions which return a set of rows/columns
As Calculate modifiers, Removing filters from column
So, let’s dive in and understand these functions one by one.
ALL
The ALL function is indeed the most important one, useful in numerous scenarios. Let me try to explain this with an example.
Scenario 1: Creating a Table with Distinct Values
See the DAX Code snippet and result below.
Suppose we want a table with distinct values from our fact table. I am using the same Financials dataset from our previous blogs.
We aim to have a Product table with distinct values from the Financials table. In this case, ALL returns a product table with distinct values.
At this point, it’s worth introducing the ALLNOBLANKROW function.
As the name suggests, this function performs the same tasks as ALL, with the key difference being that it ignores blank rows during evaluation, while ALL includes them.
Scenario 2: Calculating Profit at Category Level
In situations where we want to calculate the Profit at the Category level instead of the Product level, we’ll make use of ALL as filter remover within the Calculate function. See the below DAX code and resultant table.
Now lets understand the DAX for [Segment Profit All] measure. In the filter expression of CALCULATE, we have passed ALL(financials[Product]). It removes any active filters on Product column, whether it comes from any slicer or the visual itself.
Thus, when we bring the [Segment Profit All] measure into the matrix table with both Segment and Product in the Rows section, the filter coming from the Product column is ignored, and only the Segment-wise filter is applied. Consequently, [TotalProfit] is calculated at the Segment level.
Scenario 3: Calculating Profit at Category Level, Ignoring Certain Products
In this case, an additional filter expression is passed inside Calculate where the VTT product is ignored during the [TotalProfit] calculation. As seen in the table above, the Total Profit for each segment does not consider VTT.
Example: Channel Partners
Total Profit :13,16,803.14
VTT Profit : 2,19,765.96
Profit w/o VTT : 13,16,803.14–2,19,765.96 = 10,97,037.18
ALLEXCEPT
ALLEXCEPT is a powerful function in DAX that comes in handy when we want to apply certain filters while ignoring others during calculations.
Let’s understand this with a DAX expression where we will get the same result as we got using ALL, but the calculation method is different.
If you look at the result in the table above (highlighted in Orange), it is exactly the same as what we got with ALL (highlighted in Pink).
The only difference this time is that any filter other than the filter on Segment is ignored.
ALLEXCEPT takes two arguments. First argument is the table name and then the second argument is the column name whose filter is to be applied.
We can obviously pass the whole table also as the only argument or we can provide multiple column names separated by commas.
In this case, out of the two columns visible in the table i.e Segment and Product, filters coming from product rows are ignored and all the rows are shown with the Segment total.
TIP :
This type of calculations is also important where we want aggregation at a particular hierarchy level. For example, if we want to calculate Percentage Profit by each product within a Segment. This requires Profit of an individual product divided by their overall category Profit.
See the below DAX and table screenshot:
Conclusion
This brings us to the end of the first part of this blog, where we focused on ALL and ALLEXCEPT DAX functions. We also discussed how ALLNOBLANKROW can serve as an alternative to ALL when dealing with blanks in the data.
Stay tuned for the second part of this series, where we’ll continue our journey into the ALLxxx family of DAX functions.
Your feedback is invaluable in shaping future content. Please feel free to share your thoughts, both positive and negative, in the comments.
Connect with me through my social media handles; I look forward to hearing from you!
Understand DAX better by reading blogs from my Elevate DAX series here:
Don’t forget to subscribe to
and join our Power BI community
