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] .

'Managers' table

‘Managers’ table

‘Sales’ has a little bit more columns (although it doesn’t matter here): [Order] , [Manager] , [Amount]  and [Order type] .

'Sales' table

‘Sales’ table

As you can see, they are linked one-to-many by [Manager]  column. Quite easy.

Data Model

Data Model

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] ) ) )

Sum of sales

“Sum of sales” – everything works fine

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” )
    )
)

KPI column

Column KPI: something went wrong way.

Oops… Try again:

EVALUATE
ADDCOLUMNS (
Managers,
    “KPI”DIVIDE (
        CALCULATE ( SUM ( Sales[Amount] ) ),
        CALCULATE (
            SUM ( Sales[Amount] ),
            FILTER ( ALL ( Sales[Manager] ), Sales[Manager] = “Mike” )
        )
    )
)

Seriously?

Really, what did I thought applying the same expression again?

But wait, what is going wrong? My curious friend described this situation as he sees it:

  1. There is CALCULATE, it performs context transition from row context (we have one).
  2. 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.
  3. 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.
  4. Then we calculate a new filter (the second argument of CALCULATE). Context transition (i.e. propagated filters) doesn’t affect it.
  5. 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).
  6. 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” )
)

How much filters applied on column?

How much filters applied on column? Only one, as expected

Ok, as there only one filter applied, let’s look at it:

EVALUATE
ADDCOLUMNS (
Managers,
    “What filter?”CALCULATE ( FILTERS ( Sales[Manager] ), Sales[Manager] = “Mike” )
)

What filter is applied?

What filter is applied? Oh, it is “Mike”. What did I expected?

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”
    )
)

is this column crossfiltered

Is this column crossfiltered? Sure.

Sure it is. And what filters?

EVALUATE
ADDCOLUMNS (
Managers,
    “other filters?”CALCULATE ( FILTERS ( Managers[Manager] ) )
)

What is the other filters?

What is the other filters?
Of course it is managers from theirs rows!

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.

Occam do not approve

Useful tool, by the way

 

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!

The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI

Here is the Book for you, a wanderer!

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:

Filter propagation on expanded table

Filter propagation on expanded table

“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] .

Filter propagation during context transition

Filter propagation during context transition

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"

Filter applied

Filter applied after context transition, and there are no rows that fuilfill both conditions

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

Share this
Quick Filter for Unique Values in Power Query and Power BI
On lazy value evaluation order in Power Query and Power BI

Comments

  1. @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

  2. 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

  3. 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”
    )
    )
    )

    • 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

  4. @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

Leave a Reply

Your email address will not be published / Required fields are marked *