Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 topic
https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
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 topic
https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |