Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AlB
Super User
Super User

Filter from context transition vs previous external filter on same field

Hi all,

We have Table1 as follows:

DateAmount
01/01/20181
01/02/20180
01/03/20183
01/04/20184
01/05/20185
01/06/20186
01/07/20180
01/08/20180
01/09/20180
01/10/201810
01/11/201811
01/12/201812

Now we create a calculated table as follows. Disregard how useful this would be in a real setting.
It's just an example to illustrate what I want to ask.

CalculatedTable =
CALCULATETABLE (
    ADDCOLUMNS (
        ALL ( Table1[Date] ),
        "Result", CALCULATE ( SUM ( Table1[Amount] ) )
    ),
    Table1[Date] = DATE ( 2018, 03, 01 )
)

 

We have an "outer" filter on Table1[Date] from the CALCULATETABLE( ). When we get to the
     CALCULATE ( SUM ( Table1[Amount] ) )
we have that outer filter plus an additional filter on Table1[Date] derived from context transition. We thus have two filters on
[Date]. CalculatedTable results in this:

 

DateResult
01/01/20181
01/02/20180
01/03/20183
01/04/20184
01/05/20185
01/06/20186
01/07/20180
01/08/20180
01/09/20180
01/10/201810
01/11/201811
01/12/201812

That is, exactly the same as Table1. We get the same result regardless of what we set in the outer filter. From here, we can deduce that the inner filter on Table1[Date], i.e, the one derived from context transition, overrides the outer filter of Table1[Date] (coming from the CALCULATETABLE).

Can anybody confirm this is so and explain why it works that way? I would have expected a simple AND of the two filters instead.


Thanks very much

Code formatted with   www.daxformatter.com

2 ACCEPTED SOLUTIONS
marcorusso
Most Valuable Professional
Most Valuable Professional

The CALCULATE function performs a context transition. A column filter (Date[Date]) then overwrites an existing filter over the same column.

The lack of CALCULATE explains what you observe when you see 3 in all the rows.

The result you expect can be obtained by applying KEEPFILTERS. In order to use KEEPFILTERS vs. context transition it has to be applied over the table expression of the iterator.

 

CalculatedTable = 
CALCULATETABLE (
    ADDCOLUMNS (
        KEEPFILTERS ( ALL ( Table1[Date] ) );
        "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
    );
    FILTER ( Table1; Table1[Date] = DATE ( 2018; 03; 01 ) )
)

View solution in original post

marcorusso
Most Valuable Professional
Most Valuable Professional

Correct.

 

Marco Russo - SQLBI

View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @AlB,

 

What is happening here is that the context of the CALCULATEDTABLE expression is overwriting the filter you have, the use of the ALL expression when you create the calculated table.

 

If you check the documentation on the ALL function it refers that the function 

 

"Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table." 

 

So creating a table with an all expression will overwrite the filter you have.

 

If you rewrite your calculated table like this:

 

CalculatedTable = 
CALCULATETABLE (
    ADDCOLUMNS (
         ALLSELECTED(Table1[Date]) ;
        "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
    );
   Table1[Date] = DATE ( 2018; 03; 01 )

You will get a single value:

 

Date                Result

01/03/2018 3

 

If you rewrite the expression like this you will get: result below:

CalculatedTable = 
CALCULATETABLE (
    ADDCOLUMNS (
         ALLSELECTED(Table1[Date]) ;
        "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
    );
   DATESBETWEEN(Table1[Date]; DATE ( 2018; 03; 01 ); DATE ( 2018; 05; 01 ))
)

Date               Result

01/03/2018 3
01/04/2018 4
01/05/2018 5

 

As you can see using the ALLSELECT it will only get the results used in the outer filter.

 

This will also happen if use the full table as a parameter in the calculatedtable function:

CalculatedTable = 
CALCULATETABLE (
    ADDCOLUMNS (
         Table1 ;
        "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
    );
   Table1[Date] = DATE ( 2018; 03; 01 )
)

Date                Result     Amount

01/03/2018 3 3

 

As you can see from the different tests, the use of ALL is what is impacting your final outcome, breaking this down you are calculating a table based on all the values of the Table1 Dates then you want to filter only the 1st of march since the previous as a different context from table 1 you will not get the result you want.

 

If you redo your table with the all but apply a filter on it it will return the expected result:

 

CalculatedTable = 
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            ALL ( Table1[Date] );
            "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
        )
    );
    Table1[Date] = DATE ( 2018; 03; 01 )
)

Date                Result

01/03/2018 3

 

In this case I'm calculating the table with all the data and then appling a filter to that new table based on the context of Table1 so the result is only one line.

 

Hope this helps to clarify part of your question.

 

As a disclaimer maybe some things are not well explained and I apoligize for that, but my main point is that the use of ALL impacts the way outcome.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix

 

First of all, thank you very much for the quick and extensive answer. I truly appreciate the effort. 

Now my question, unless I've misunderstood something, remains unanswered. 

 

The ALL( ) in my code does not remove any filter. It just returns a table with no filters applied.It sounds like the same but it cetainly ain't. There's a very important difference between the two ways in which ALL() can act: filter-remover or function returning a table. The latter is at play here. Check out this very interesting article by the Italian gurus for further details.

 

The "outer" filter is untouched in this case. You can confirm that by trying:

 

CalculatedTable_2 = 
CALCULATETABLE (
    ADDCOLUMNS (
        ALL ( Table1[Date] );
        "Result";  SUM ( Table1[Amount] ) 
    );
    Table1[Date] = DATE ( 2018; 03; 01 )
)

where we've removed  the CALCULATE. What we get here is:

DateResult
01/01/20183
01/02/20183
01/03/20183
01/04/20183
01/05/20183
01/06/20183
01/07/20183
01/08/20183
01/09/20183
01/10/20183
01/11/20183
01/12/20183

 

As you can see, the "outer" filter is still very much alive, and kicking. ALL( ) has no effect on it.

 

For may initial code (first post), the behaviour that I expected would yield this result for CalculatedTable (mostly blanks):

 

DateResult
01/01/2018 
01/02/2018 
01/03/20183
01/04/2018 
01/05/2018 
01/06/2018 
01/07/2018 
01/08/2018 
01/09/2018 
01/10/2018 
01/11/2018 
01/12/2018 

 

This would be the case if an AND was carried out between the outer filter (CALCULATETABLE()'s) and the inner filter (derived from context transition) on Table1[Date]. Since the result is what I showed earlier, I can only conclude that the filter resulting from the context transition overrides the outer filter.

I would like someone to either confirm that or otherwise point to flaws in my reasoning (if any).

 

Many thanks

 

Hi @AlB,

 

Believe that the answer is on the final part of the article you refer.

 

Check the part of the article where they have the image with the crossing of the table of color Red and PercProductsSold, they refer that the all used with CALCULATE removes filters, however they continue to make several changes to the context of the calculations, and on the last part they refer to ALL and CALCULATE table.

 

Making use of the article I was abble to get to the calculation below where the result is the blakns in all rows exccept on the march value:

 

CalculatedTable_2 =
CALCULATETABLE (
    ADDCOLUMNS (
        ALL ( Table1[Date] );
        "Result"CALCULATE ( SUM ( Table1[Amount] ) )
    );
    FILTER ( Table1; Table1[Date] = DATE ( 20180301 ) )
)

DateResult

01/01/2018 00:00:00  
01/02/2018 00:00:00  
01/03/2018 00:00:00 3
01/04/2018 00:00:00  
01/05/2018 00:00:00  
01/06/2018 00:00:00  
01/07/2018 00:00:00  
01/08/2018 00:00:00  
01/09/2018 00:00:00  
01/10/2018 00:00:00  
01/11/2018 00:00:00  
01/12/2018 00:00:00  

 

But let's ask @marcorusso or @AlbertoFerrari, can you please explain the way the inner filter  and outer filter are interacting with the CALCULATE table.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



marcorusso
Most Valuable Professional
Most Valuable Professional

The CALCULATE function performs a context transition. A column filter (Date[Date]) then overwrites an existing filter over the same column.

The lack of CALCULATE explains what you observe when you see 3 in all the rows.

The result you expect can be obtained by applying KEEPFILTERS. In order to use KEEPFILTERS vs. context transition it has to be applied over the table expression of the iterator.

 

CalculatedTable = 
CALCULATETABLE (
    ADDCOLUMNS (
        KEEPFILTERS ( ALL ( Table1[Date] ) );
        "Result"; CALCULATE ( SUM ( Table1[Amount] ) )
    );
    FILTER ( Table1; Table1[Date] = DATE ( 2018; 03; 01 ) )
)

@marcorusso

OK, so in general, a filter resulting from context transition on ColumnA will overwrite any previous filter there was on ColumnA,  correct?

 

Thus in my initial code (see below), the filter on Table1[Date] resulting from context transition within the CALCULATE( ) will overwrite the  theretofore existing filter on Table1[Date] (originated in the  CALCULATETABLE( )). Correct?

 

Many thanks  

 

CalculatedTable =
CALCULATETABLE (
    ADDCOLUMNS (
        ALL ( Table1[Date] ),
        "Result", CALCULATE ( SUM ( Table1[Amount] ) )
    ),
    Table1[Date] = DATE ( 2018, 03, 01 )
)

 

 

 

marcorusso
Most Valuable Professional
Most Valuable Professional

Correct.

 

Marco Russo - SQLBI

@MFelix

 

Cool. Your latest example is still consistent with my hypothesis.

 

 FILTER ( Table1; Table1[Date] = DATE ( 20180301 ) )

returns a one-row table

Date                     Amount

01/03/2018          3

 

so we have "outer" filters on both columns, [Date] and [Amount]. When we get to the CALCULATE( ), the inner filter on [Date] (resulting from the context transition) overrides the outer filter. But we still have a filter active on [Amount] and there's a sole row with Amount = 3 so only in that row is the SUM( ) non-blank.

 

By the way, I'm not really interested in getting to any result in particular but rather in an explanation to the behaviour we're seeing. The examples are a means to an end.

 

Thanks 

Hi @AlB,

 

I'm also trying to stimulate discussion and get to understand this behaviour. That's why I added Marco Russo and  Alberto Ferrari to the discussion let's see if the master's can help us out.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.