Forum Discussion
Helper Iwant to know the difference
what is the difference between CALCULATE(SUM('Fact Sale'[Quantity])) and SUM('Fact Sale'[Quantity])?
@Hi Herndon_powerbi
The only difference between these expressions is that, in the expression with CALCULATE, context transition occurs before the SUM(...) expression is evaluated.
"Context transition" means all row contexts are converted into equivalent filters.
This means that these expressions would return different results only if one or more row contexts existed, such as if they were evaluated in a calculated column or within the row context of an iterator.
For example, if you created calculated columns with these expressions in (say) a Customer table, the expression with CALCULATE would convert the current row of the Customer into an equivalent filter, and would give you something like the sum of Quantity for that Customer. Without CALCULATE, the expression would return an unfiltered sum of Quantity.
One thing to note is that a reference to a measure automatically "wraps" the measure's expression in CALCULATE.
This is a good article on this topic:
https://www.sqlbi.com/articles/understanding-context-transition/
Regards,
Owen
3 Replies
- bcdobbs
Community Champion
CALCULATE does a few things. It can add and remove filters. However it's big power is "Context Transition" which takes the current row ("Row Context") and adds all of it's columns as filters (adds them to the "Filter Context")
If you use SUM('Fact Sale'[Quantity]) in a calculated column in a product table it will return the total of all quantities in the fact table.
If you use CALCULATE ( SUM ('Fact Sale'[Qantity] ) in a calculated column in a product table it will return the total of quantites in the fact table filtered by that product.
That's the most obvious place you'd see the difference. However you also get a row context in an iterator like SUMX so would need to use CALCULATE within the expression there if you needed to move the row into a filter. (Note an explicit measure [Total Quanity] has an implicit calculate).
This probably explains it better than me: Understanding Context Transition - SQLBI - OwenAuger
Super User
@Hi Herndon_powerbi
The only difference between these expressions is that, in the expression with CALCULATE, context transition occurs before the SUM(...) expression is evaluated.
"Context transition" means all row contexts are converted into equivalent filters.
This means that these expressions would return different results only if one or more row contexts existed, such as if they were evaluated in a calculated column or within the row context of an iterator.
For example, if you created calculated columns with these expressions in (say) a Customer table, the expression with CALCULATE would convert the current row of the Customer into an equivalent filter, and would give you something like the sum of Quantity for that Customer. Without CALCULATE, the expression would return an unfiltered sum of Quantity.
One thing to note is that a reference to a measure automatically "wraps" the measure's expression in CALCULATE.
This is a good article on this topic:
https://www.sqlbi.com/articles/understanding-context-transition/
Regards,
Owen
sokgSolution Supplier
It depends.
Where do you use this measure???
In a calculated column???Regards,
Sokratis
