Forum Discussion
Community ChampionFilter from context transition vs previous external filter on same field
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 ) ) )
Community ChampionHi 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:
| Date | Result |
| 01/01/2018 | 3 |
| 01/02/2018 | 3 |
| 01/03/2018 | 3 |
| 01/04/2018 | 3 |
| 01/05/2018 | 3 |
| 01/06/2018 | 3 |
| 01/07/2018 | 3 |
| 01/08/2018 | 3 |
| 01/09/2018 | 3 |
| 01/10/2018 | 3 |
| 01/11/2018 | 3 |
| 01/12/2018 | 3 |
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):
| Date | Result |
| 01/01/2018 | |
| 01/02/2018 | |
| 01/03/2018 | 3 |
| 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
Super UserHi 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 ( 2018; 03; 01 ) )
)
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
- marcorusso
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 ) ) )- AlB
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
- AlB
Cool. Your latest example is still consistent with my hypothesis.
FILTER ( Table1; Table1[Date] = DATE ( 2018; 03; 01 ) )
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