In this video, we’ll continue learning about DAX. Let’s take a look at the function CALCULATE, which is probably one of the most important formulas in DAX.
There are many different ways to use this function. In this article, we’ll discuss the basic use cases.
The CALCULATE function evaluates a given expression or formula under defined filters. The syntax of the function is following:
- CALCULATE(<expression>, <filter1>, <filter2>…)
The expression part can be either another DAX function, such as SUM, COUNT, or an existing measure.
Filters in CALCULATE
Filters can be the following:
- Boolean filter expressions (TRUE or FALSE)
- Table filter expressions
- Filter modification functions
You can use multiple filters by typing AND (&&) logical operator, meaning all conditions must be TRUE, or by OR logical operator, which means either condition can be true.
The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It’s required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.
Let’s say, we’d like to calculate the sum of sales in a particular city. We have a fact table for sales and a column for city. In this example, we can use the following formula:
- Sales in London = CALCULATE ( SUM (Sales), [City] = “London”)
Excel users can relate the DAX CALCULATE function to Excel’s SUMIF or COUNTIF. Therefore, if you understand the SUMIF logic in Excel, CALCULATE should not be hard for you.
The CALCULATE function is very helpful when creating more complex calculations that allow for more in-depth insights. This DAX function is one of the more foundational functions for analysts, please watch the video below to get a better understanding of CALCULATE.