What does it mean to be crossfiltered?
The history of “reinventing the bicycle” using DAX
Defining evaluation context and context transition rules is the most important and confusing part of DAX. Sometimes when you think you’ve already managed it, DAX turns to you with other side, hook, uppercut – and you’re knocked down.
Last week one of my Facebook friends asked me to explain why his measures working this way and not that way. It was quite easy questions and there is no sense to place them here. But my friend is a very curious man, one question led to another, and suddenly I found that I can’t explain a very simple, on the first look, concept. The question was about filtering under context transition in calculated column (here you can imagine a very big grin on DAX’s face).
For this post I reworked and simplified data model.
There are two tables, named ‘Managers’ and ‘Sales’ . ‘Managers’ has only two columns: [Manager] and [Department] .
‘Sales’ has a little bit more columns (although it doesn’t matter here): [Order] , [Manager] , [Amount] and [Order type] .
As you can see, they are linked one-to-many by [Manager] column. Quite easy.
First of all my friend asked me, how context transition works. “Hey, it’s easy!” – I said, and then refer him to all perfect descriptions of context transition we could find on the Internet.
In brief, CALCULATE:
- copies existing filter context
- then applies row contexts one by one on it as filters
- then calculates new filters if they existed (in old context), and these new filters can rewrite these applied after context transition.
- After all this operations with filters are finished, CALCULATE evaluates ‘expression’ part.
Or something like that.
“Ok,” – my friend continues. – “We would like to add a calculated column to the ‘Managers’ table, which would show the sum of sales per manager”. No problem:
EVALUATE
ADDCOLUMNS ( Managers, “Sum of Sales”, CALCULATE ( SUM ( Sales[Amount] ) ) )
It works, excellent!
Ok then. “One of the managers, Mike, is a SuperStar (he really is, for sure). Everybody wants to be like Mike, and his sales level is a KPI for others. So the chief of sales would like to know, what is the other managers performance on this KPI”.
Why, it’s easy, isn’t it? We already have the sum of sales per manager, and all we need is to divide their sales by Mike’s.
EVALUATE
ADDCOLUMNS (
Managers,
“KPI”, DIVIDE (
CALCULATE ( SUM ( Sales[Amount] ) ),
CALCULATE ( SUM ( Sales[Amount] ), Sales[Manager] = “Mike” )
)
)
Oops… Try again:
EVALUATE
ADDCOLUMNS (
Managers,
“KPI”, DIVIDE (
CALCULATE ( SUM ( Sales[Amount] ) ),
CALCULATE (
SUM ( Sales[Amount] ),
FILTER ( ALL ( Sales[Manager] ), Sales[Manager] = “Mike” )
)
)
)
But wait, what is going wrong? My curious friend described this situation as he sees it:
- There is CALCULATE, it performs context transition from row context (we have one).
- Actually CALCULATE takes existing filter context (there are no actual filter context, we are in the raw table, not in a pivot table), and applies current row context as filter.
- As tables are linked, then filter, applied on ‘Managers’[Manager] , has to be propagated to ‘Sales’[Manager] . So the ‘expression’ argument of CALCULATE currently is under filter, propagated from transited row context.
- Then we calculate a new filter (the second argument of CALCULATE). Context transition (i.e. propagated filters) doesn’t affect it.
- So, as the first step of FILTER, we clearing all existing filters on ‘Sales’[Manager] with ALL(‘Sales'[Manager]) , then apply a new one (‘Sales’[Manager]=”Mike”).
- After context transition we have a filter, propagated on ‘Sales’[Manager] . So this new one (from the FILTER part) ought to overwrite propagated one, yes? But it didn’t.
(if you’ve mastered DAX, you can see gaps and mistakes there, but for the most of DAX users all seems correct, doesn’t it? 😉 )
Oh… Why, really?
As far as we know, if filter argument of CALCULATE conflicts with existing filter context, it overwrites it. If a new filter affects other column, then existing filters and “calculated” one will work as if “AND” was used.
And… It seems like both propagated filter and the new filter have to be applied on the same column, so latter has to overwrite former, but they acts as if there is intersection and “AND” condition was used.
Confused? I am. I tried to explain it with many words but no, no way. Otherwise, my friend asked more and more, and I became suspicious that my knowledge is very weak, and I need a consultation. With all these perplexed minds I went to the Matt Allington forum and asked him a lot of questions. I translated my doubts and uncertainties to him and yes, I confused him a little too. But he was very kind and endurant, and set all things on their place (Matt, thanks again!).
But some concepts were still unclear, so I opened a DAX Studio and started my researches.
What if there are other filters, applied on this column (mysterious hidden filters, I mean)?
EVALUATE
ADDCOLUMNS (
Managers,
“How much filters?”, CALCULATE ( COUNTROWS ( FILTERS ( Sales[Manager] ) ), Sales[Manager] = “Mike” )
)
Ok, as there only one filter applied, let’s look at it:
EVALUATE
ADDCOLUMNS (
Managers,
“What filter?”, CALCULATE ( FILTERS ( Sales[Manager] ), Sales[Manager] = “Mike” )
)
Nothing suspicious. There is one filter, it is “Mike”.
So, we went wrong way. Is this column still crossfiltered?
EVALUATE
ADDCOLUMNS (
Managers,
“is crossfiltered?”, CALCULATE (
IF ( ISCROSSFILTERED ( Sales[Manager] ), “yes”, “no” ),
Sales[Manager] = “Mike”
)
)
Sure it is. And what filters?
EVALUATE
ADDCOLUMNS (
Managers,
“other filters?”, CALCULATE ( FILTERS ( Managers[Manager] ) )
)
Ok, where we are? That’s why we can’t see any sales for “John”: [Manager] could not be “John” AND “Mike” at the same time (…actually someone could be “John Michael Montgomery” for example, but no, not now). We see that DAX really sure that ‘Sales'[Manager] is crossfiltered and filtered at the same time. This lead us to the question: what does it mean to be crossfiltered?
Although it is very interesting question, it looks a little psychiatric, if you apply it to the man. So don’t try it at home, please.
And now is the real story of “bicycle’ reinvention”
I know that filter is actually applied on the column, not a table.
I took a pen and draw a little diagram: ‘Managers'[Manager] is filtered on “John”, then this filter propagates to ‘Sales'[Manager] . That’s mean that ‘Sales'[Manager] also filtered on “John”, didn’t? J
Then I draw small one-column table on top of ‘Sales'[Manager] : ‘Sales'[Manager]=”Mike” . Well… This filter also acts on that column, we’ve seen it on previous steps.
So, something wrong was happens not there, but when we see at crossfiltering part. After some reflection, I came to these suggestions:
- Whether DAX knows that ‘Sales'[Manager] is linked and did not overwrite filter on it. In other words, filter overwriting doesn’t work when column is linked from “many” side of relation,
- Or there is another column that is the same as ‘Sales'[Manager] which is still filtered by filter propagation…
But… If linked column is blocked for overwriting filters somehow, hence we shouldn’t be able to overvrite any filter on it, or DAX knows that there is other filter… Oh my, you see? It is too complex to be truth. Occam will not approve this.
So… That is it! There is another column, that’s why DAX used intersection of two filters! May be DAX creates this copy when we define relations?
Sounds weird. But I tested it many times, sure it so: there another copy of this column!
I do not know what were the correct words of real bicycle inventor, so I acted like Galileo: “And yet it moves!!!” Or even like Archimedes: “Eureka!”
Then I tried to find a confirmation for my invention. And opened “The Definitive Guide to DAX” by Alberto Ferrari and Marco Russo.
Here the glorious history of bicycle invention ingloriously ends
Why on Earth I did not read that part of the Book before!
I was close. Very close. They are already in the model, with that magnificent “expanded table” concept.
With this concept, all tables, related to another ones on “many” side, actually has all columns from their lookup tables, like this table is expanded. So my ‘Sales’ table, as it is related to ‘Managers’ on “many” side, already has all columns of ‘Managers’ in its expanded version.
So the real picture of ‘Sales’ is:
“All columns” means ALL. ‘Sales'[Order] , ‘Sales'[Manager] , ‘Sales'[Amount] , ‘Sales'[Order type] , ‘Managers'[Manager] , ‘Managers’ [Department] .
And when (after context transition) filter propagates from the ‘Managers’ to the ‘Sales’ , this propagation affects only these two columns of ‘Sales’ expanded version: ‘Managers'[Manager] and ‘Managers’ [Department] .
So I can clear or set filters from ‘Sales'[Manager] all the way. In any order. It do not affect ‘Managers'[Manager] filters.
And thats why these two filters intersects, not overwriting each other. And there no rows where ‘Sales'[Manager]=”Mike” could be intersected with ‘Managers'[Manager]=”John”
Actually it has to be a very first chapter in any DAX book. Written in CAPITALS 🙂
So, to pull Mike’s sales into each row calculations, we need it first to clear all filters from ‘Managers’ (we remember that row context becomes filters on each column in ‘Managers’ ), then apply filter on ‘Managers'[Manager] column, and that’s all:
EVALUATE
ADDCOLUMNS (
Managers,
“KPI”, DIVIDE (
CALCULATE ( SUM ( Sales[Amount] ) ),
CALCULATE (
SUM ( Sales[Amount] ),
FILTER ( ALL ( Managers ), Managers[Manager] = “Mike” )
)
)
)
Or on ‘Sales'[Manager] column:
EVALUATE
ADDCOLUMNS (
Managers,
“KPI”, DIVIDE (
CALCULATE ( SUM ( Sales[Amount] ) ),
CALCULATE ( SUM ( Sales[Amount] ), ALL ( Managers ), Sales[Manager] = “Mike” )
)
)
PS:
I understand that this formula and this story are not ideal, but they show how we should think when we want to operate with filters on linked columns.
And… Even if I have colored the details about my way to the “invention of bicycle”, I did really invented it! 😀
Follow me:Share this: