Ciao friends. I have a new DAX Puzzle for you.
Here is a model where we have three tables and two measures,
a few relationships. The puzzle is:
what is the result of the All Tokens measure in the table that you see
here. So take your time. Think about the solution. in a few seconds,
I will provide the solution. So, press pause.
Are you ready? Okay, let's go.
So what happens here? When we have this situation, we have,
let's start with the Tokens measure. The COUNTROWS Tokens works this way. We have,
for example, in the first row we have A selected here,
which means that we have in the feed context, the filter for A,
in the Name column, and the presence of this filter filters,
associations, because we have A A A that filters T1, T4, T5
and the filter because of the bi-directional filter the filter propagates to
Tokens, T1, T4, T5. For this reason,
COUNTROWS of Tokens produces the result of three that you see here.
Now, what happens when you execute the same measure,
applying in the filter context, this All Tokens, ...
filter modifier. Well,
ALL Tokens removes the filter from the expanded table Tokens and the
expanded table Tokens is only this one.
We don't have many to one relationship from Tokens to other tables.
And so this means that the filter that we have in Name is still there.
When we execute ALL Tokens,
we get as a result exactly the same result that we had for Tokens,
because this ALL Tokens, we remove only filters applied to the Tokens table.
So we have three, two, one,
and five The result of All Tokens is identical to
Tokens in this example. So this is the solution to the puzzle.
Now you probably say, wait a minute,
but ALL should remove all the filters should be returning the entire table.
Well, ALL used as a filter modifier in CALCULATE removes the
filters. But if we use ALL as a table function,
the result is different. So let's take a look at this example.
Here we have All Tokens 2,
which is a COUNTROWS of All Tokens. In this case,
ALL Tokens is a table function. As a table function,
It always retrieves the entire content of the Tokens table
And so the result is five, five, five, five,
no matter what, because in this case, the filter context is totally ignored.
What if we want to obtain a CALCULATE using
ALL,
removing the filters also from the other tables that could affect Tokens
in an indirect way because of the bi-directional filter.
This is what we can do using ALLCROSSFILTER. If we use ALLCROSSFILTER,
what happens?
ALLCROSSFILTER removes the filter from Tokens, from Associations,
and from Names,
which means that if I repeat the same exercise I did before.
So I have a filter contest here with just A,
which is the filter for the column Name here at this point,
when I execute here,
my CALCULATE with ALLCROSSFILTER, ALLCROSSFILTER removes the filter here, here,
and here. Now without this filter,
the result of the table Tokens is the entire table.
So if I use this version here,
ALLCROSSFILTER Tokens, I will still get five, five, five,
five.
Now, as you see the important lesson here is that ALL used as a
filter modifier has a different behavior than ALL used as a
table function.
And for this reason we suggest to not use ALL in CALCULATE.
Now we have a specific function that makes it clear
what the purpose of the filter modifier is,
and this function is called REMOVEFILTERS.
It has exactly the same, the same parameters, the same behavior,
It has the same behavior of ALL, but it can be used only in CALCULATE.
And this way, when you read the code,
you immediately realize: I'm just removing the filter - so relationships are
important - or I'm getting the entire table, no matter what.
So I hope that you liked this DAX puzzle. In any case, enjoy DAX!