In my previous post, I wrote about ALL and FILTER functions in DAX. And I mentioned that ALL is usually used as an argument in FILTER.
When do you combine FILTER and ALL in a DAX formula.
Many DAX beginners do not understand when to introduce ALL into their formula, especially within the FILTER function.
It is my hope that this brief write-up will enlighten you.
I am making use of the Super Store Sample Data
There is always a Starting Filter Context
Every value within any visual in your report has an initial filter context, based on all the selections represented by that value. And this filter is typically what is visible to any measure we write.
For example, looking at the image above. When calculating the value for West, internally, the table available to SUMX is a table where only West is visible. All other regions filtered out.
If we want to calculate for example, only Furniture Revenue. Not the entire Revenue for each Region, we will need that first argument of SUMX: “Orders” to be filtered to only Furniture.
We can write a modified version of SUMX(Orders, Orders[Sales]) to something like SUMX(FILTER(Orders, Orders[Category] = “Furniture”), Orders[Sales]).
Respecting the filters for each region, the revenue for Furniture was rightly computed.
Computing for values outside the starting filter context
What if, we now need to get percentage share of the Furniture in each region against the total Furniture sales.
Remember there is always an initial filter context based on current selections. And for each region in that report, DAX internally filters out all the other regions so there is no way to get their Furniture Revenue.
And we do need the values for all the regions to get our Grand Total of Furniture Revenue, which will be required in the percentage share measure.
Combine Filter With ALL
To solve the challenge above, we need DAX to have access to all the Furniture regardless of the selected region.
To do that, we introduce ALL to look beyond the values that have been selected in the filter and just show all the Furniture.
Our formula can then change from:
SUMX(FILTER(Orders, Orders[Category] = “Furniture”), Orders[Sales])
to
SUMX(FILTER(ALL(Orders), Orders[Category] = “Furniture”), Orders[Sales]).
We can then go ahead with our division to get the percentage share by writing a new measure as DIVIDE([Furniture Revenue], [Grand Total Furniture Revenue], 0).
Conclusion
Whenever you need values outside of the currently selected item(s) in your report, remember the ALL function that will enable you see it all. Rather than be limited to only visible rows of data.
