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 ) ) )
Super UserHi 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