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