- ALLSELECTED is likely to be
the most complex DAX function of the entire DAX language.
That is why we are not going to provide
a full description of what it does.
Instead, specifically for ALLSELECTED,
we will focus on the use cases of ALLSELECTED
understanding when to use it
and understanding how to use it safely
without incurring in the dangers,
which are very well hidden in ALLSELECTED.
Let's start by understanding when to use it.
Look at the report we have on the screen.
I am slicing by brand the sales amount
and I'm computing the percentage
which is the division of sales amount
divided by the grand total of sales amount.
The calculation is already there,
and it's a division of sales amount
by CALCULATE sales amount and I'm using ALL product
to get rid of the filter from the product table
and retrieve the grand total.
Everything is working fine but I might be in trouble
or at least I might obtain inaccurate results,
if I start using a slicer.
Focus on the value of 6.85% which is the value
of the percentage of A. Datum against the grand total.
If I use the slicer to filter some of the brands,
you see that the value for A. Datum did not change.
It is still 6.85%, and the grand total does not show 100%.
That is accurate because what I'm doing here is
I'm using ALL to get rid of any filter on the brand.
By removing any filter, I remove both the filter
which is coming from the current visual,
but also the filter which is coming from the outside slicer.
If this is not what I want,
if I want to get rid of the filter from the current visual
but still keep the outer filter
the one coming from the slicer,
then what I can do is use ALLSELECTED instead of ALL.
By using ALLSELECTED, now,
this CALCULATE will remove the filter coming from the brand
but will keep the filter coming from the outside slicer.
And if I hit Enter, now you see that
the value for A. Datum changes
because now it's two millions divided by
17 millions, no longer 30 millions.
So this is the main use case of ALLSELECTED.
Let's now take a look in more detail, at
the use that you can do with ALLSELECTED.
The percentage we computed is sales amount
divided by CALCULATE sales amount with ALLSELECTED product.
ALLSELECTED has all the functions of the entire,
ALL family of functions, can be used
both as a CALCULATE modifier or as a table function.
Even though it is most likely to be used
as a CALCULATE modifier,
and this is what we have done in the formula.
ALLSELECTED can be used with a table
and can also be used with an individual column
or with no argument at all
as we can do with REMOVEFILTERS or with ALL.
With one column, it restores
the outer filter context on one column only.
Without arguments, it restores
the outer filter context on the entire data model.
Speaking about ALLSELECTED, we said that it can be used both
as a CALCULATE modifier or as a table function.
If you use it as a table function,
it retrieves the ALLSELECTED product
that is the products as they were visible
in the outer filter context.
Here, we filter by category, and so the results are...
the count of all the products in the category Audio.
I said in the introduction that
ALLSELECTED is actually much more complex
than it looks at first sight.
Indeed, ALLSELECTED works by using shadow filter context,
and actually retrieving the list of values
of a column that is currently being iterated.
Its behavior is very complex,
and if you are interested in all the details,
you can go at this link and there you will find
a very long article that describes
at a very high level of detail,
all the information about ALLSELECTED.
And this is just to give you an idea
of what ALLSELECTED does.
In this code, I have ADDCOLLUMNS, which is an iterator,
which is running inside CALCULATETABLE.
CALCULATETABLE places a filter for the category
to be Audio or Computer.
So the filter context under which ADDCOLUMNS runs
outer filter context of ADDCOLUMNS is Audio and Computer.
And then it iterates over ALL product category,
ALL ignores the filter context,
it is used as a table function here.
And we compute the sales amount and
the sales amount using ALLSELECTED over product category.
Now, common sense would say that this ALLSELECTED restores
the filter context which was coming from outside,
but this is not what is happening.
ALLSELECTED actually does not access to this filter context
instead it restores the filter context
which is currently being iterated,
that is ALL product category.
If we run the query, and we increase the font a bit,
you see that the sales amount of Audio
is the value of sales amount of Audio,
but sales sel, is not the sum of
Audio and Computers that will be around seven millions
instead it's 30 millions.
That is the grand total of all sales,
because the filter context restored by ALLSELECTED
is the one iterated by ADDCOLLUMNS.
And this is the reason why it is extremely dangerous
to use ALLSELECTED if you are inside an iterator
or if any kind of iteration happen outside of your code.
If your code is running inside the iteration
created by Power BI in order to build the visual,
then ALLSELECTED does exactly what you would expect from it.
But if you start iterations before ALLSELECTED,
then the behavior is correct
but extremely hard to understand.
So this leads us to the rules to use ALLSELECTED.
It is safe to use ALLSELECTED as one of the arguments
of CALCULATE; it is kind of dangerous
using ALLSELECTED as a table function
because even though it works,
and it does exactly what it is expected to do,
understanding the semantic is kind of hard.
Another important detail is that you can use
ALLSELECTED if it is a CALCULATE modifier
used very early in the measure definition.
If you have CALCULATE whatever you want
using ALLSELECTED, that is good, it's safe,
and you can rely on its behavior.
If you do CALCULATE SUMX over product
and then another CALCULATE and inside that
you put ALLSELECTED, then you just went too far,
so ALLSELECTED is nested deep inside your measure
and its behavior becomes really hard
to understand and follow.
And as a rule of thumb,
never use ALLSELECTED inside an iteration
as we did in the previous example,
because if you use ALLSELECTED inside an iteration,
the filter context you retrieve
is not the outer filter context
but it is the filter context defined by the whole iteration.