# 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! 😀

sam

April 29, 2017 - 13:56

@Maxim – Can you explain in the below dax query produces the same results as Filtering on Sales[Manager]=”Mike” – The filter argument of calculate should have cleard the filter coming from the currently iterated row,

EVALUATE

ADDCOLUMNS (

Managers,

“KPI”, DIVIDE (

CALCULATE ( SUM ( Sales[Amount] ) ),

CALCULATE ( SUM ( Sales[Amount] ), Managers[Manager] = “Mike” )

)

)

Maxim Zelensky

May 2, 2017 - 21:19

Hi Sam!

It is interesting question, but now I’m quite busy, will answer a little bit later.

Cheers,

Maxim

Nazeerul Hazard

January 29, 2017 - 09:44

How about following code using variable

Var MikeKPI = CALCULATE ( SUM ( Sales[Amount] ), Sales[Manager] = “Mike” )

Return

ADDCOLUMNS (

Managers,

“KPI”, DIVIDE (

CALCULATE ( SUM ( Sales[Amount] ) ),

MikeKPI )

)

Maxim Zelensky

January 30, 2017 - 09:00

Hi, Nazeerul

Thanks for sharing, nicely done.

I’ve used Excel 2013 when wrote this examale, and there is no Var/Return option. But for Excel 2016 or SSAS/Power BI your code is very helpful

Maxim

Jorge

November 21, 2016 - 20:54

You do not need the FILTER function you only need:

EVALUATE

ADDCOLUMNS (

Managers,

“KPI”, DIVIDE (

CALCULATE ( SUM ( Sales[Amount] ) ),

CALCULATE (

SUM ( Sales[Amount] ),

ALL ( Managers ),

Managers[Manager] = “Mike”

)

)

)

Maxim Zelensky

November 22, 2016 - 00:49

Hi Jorge!

Thanks for the comment!

I thought you didn’t see my last formula, but then understood what did you meant.

Of course, you are right. I think, we can find a little bit more ways to calculate this measure, and these was just for illustration purposes.

Thanks again

Cheers,

Max

sam

November 6, 2016 - 14:34

@Maxim

Nice Article

Below is an another excellent article on cross filtering in DAX

The Logic behind the Magic of DAX Cross Table Filtering

Maxim Zelensky

November 6, 2016 - 19:38

Hi Sam!

Thanks a lot!

I’ve seen this article before, but it suddenly didn’t jump in my mind when I fight with that issue. Then when I’ve written this post I remember about this article, but it seems too tech for me at the moment.

Anyway, this article is a really piece of tech art, agreed

Cheers,

Max