Forum Discussion
Calculate is escaping the filter context?
Hi,
I have 2 test formulae:
Cumulative Volume = CALCULATE(sum('Product'[Volume]),filter(all('Product'),
'Product'[Date] <= max('Product'[Date])
))
Total Volume = CALCULATE(sum('Product'[Volume]),
FILTER(
all('Product'),
'Product'[Date] <= CALCULATE( max('Product'[Date]) )
)
)
Which give the following outputs:
The first formula successfully calculates the cumulative total and the second one returns the grand total. For the second formula please can you tell me why CALCULATE( max('Product'[Date]) ) returns the last date in the date column, rather than the current date? I thought CALCULATE inherits the existing filter context - given that no filters are in this CALCULATE expression please can you tell me why it gives a different result to max('Product'[Date])?
Thanks for your help,
CM
Anonymous
Calculate function transforms row context to Filter context.
In your first formula the filter context is provided by the Table/Matrix visual you are using.
In the second case, CALCULATE transforms the row context of the ALL(Product) table into a FILTER context and is assessed/computed in this sub context.
Check out the SQLBI website. There are lot of articles on this topichttps://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
1 Reply
Anonymous
Calculate function transforms row context to Filter context.
In your first formula the filter context is provided by the Table/Matrix visual you are using.
In the second case, CALCULATE transforms the row context of the ALL(Product) table into a FILTER context and is assessed/computed in this sub context.
Check out the SQLBI website. There are lot of articles on this topichttps://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/