Forum Discussion
What does CALCULATE() do when you leave the filter arguments blank?
I am trying to understand the CALCUALTE function. I have a pretty good idea of why it is useful and even some cases of when to use it,
However I understand that you do not need to provide a filter arguement in the CALCULATE function:
So you can write something like
CALCULATE(X) where X is some expression.
Now to me CALCULATE is used to modify filter contexts on where an expression is evaluated, so if you wanted to look at sales of bikes in China you could well write something like
CALCULATE(SUM('Sales'[SaleAmount],'Sales'[Type] = "Bike", 'Sales'[Country] = "China")
But I am failing to see why calculate is useful without giving any filters.
Clearly I am missing something here.
Can anyone explain?
Hi cliveb2016
Calculate without filter argument does a very important job
"It transforms existing row context into a filter context"
For example inside a calculated column, see this post
http://www.excelnaccess.com/context-transition-using-calculate/
And even in a MEASURE where for example ROW context is introduced by an ITERATOR
Thus the MEASURES below would normally give different results
Measure = SUMX ( Table, CALCULATE ( SUM ( Table1[Column] ) ) )
Measure = SUMX ( Table, SUM ( Table1[Column] ) )
5 Replies
- Zubair_Muhammad
Community Champion
Hi cliveb2016
Calculate without filter argument does a very important job
"It transforms existing row context into a filter context"
For example inside a calculated column, see this post
http://www.excelnaccess.com/context-transition-using-calculate/
And even in a MEASURE where for example ROW context is introduced by an ITERATOR
Thus the MEASURES below would normally give different results
Measure = SUMX ( Table, CALCULATE ( SUM ( Table1[Column] ) ) )
Measure = SUMX ( Table, SUM ( Table1[Column] ) )
Thanks for the answer I understand now.
One follow up question:
If you add the filter arguments does that change the fact that calculate propagates the row context to filter context?
Thanks,
- Zubair_Muhammad
HI,
Even if you add the filter arguments.....it won't affect the transformation of RowContext to FilterContext
- Ashish_Mathur
Super User
Hi,
There are generally two triggers to use the FILTER() function:
- When using rish filters i.e. any comparison other then "column to a fixed value". Therefore a FILTER() function is to be used when comparing measure with measure, measure with fixed value, column with measure or column with column; and
- When you want to resolve a conflict i.e. when there is a conflict between fields that you have dragged to the visual and a condition that you have passed to the CALCULAte() function. If you want to give precedence to the fields dragged to the visual, then the FILTER() function has to be used.
