Forum Discussion
Filter 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 ) ) )
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 ( 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
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 ) )
)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 ) )
