The Calculate Function In DAX
The calculate function in DAX is one of the most widely used DAX functions.
It can both be very useful on its own and also be used as a container for far more complex calculations because it can modify the filter context of an expression.
It is very widely used and mastering it will be key to developing your DAX knowledge.
The Calculate Function
-
Description
The Calculate function evaluates a given expression or formula through a set of filters.
It functions very similarly to Microsoft Excel’s SUMIF, COUNTIF, and AVERAGEIF functions. These produce sum, count, or average functions subject to certain filters or conditions being met.
Look at this simple situation in Microsoft Excel to get a better understanding of it.
The aim here is to add up all the sales from the West region.
This is a perfect use case for the SUMIF formula, which will search the SALES column and calculate the total sales value only for those rows where the REGION filter or criteria equals West.
The distinction between typical SUMIF, COUNTIF, or AVERAGEIF and CALCULATE is the ability of calculate to evaluate measures based on any type of calculation.
I find it helpful to think of it as “CALCULATEIF.”
-
Syntax & Parameters
The syntax and parameters of the DAX calculate function are described here.
The EXPRESSION parameter of the calculate function is the name of an existing measure or a DAX formula that can be used to generate a valid measure.
The FILTERS, or collection of filters, is the second component of the function, and it must be a list of basic Boolean (True/false) filter expressions, or criteria that must be met (true or false).
PRO TIP: In the filter parameter of the calculate function, you cannot insert complex filters or use filters that reference measures.
What is Filter Context?
Understanding filter context is critical.
Filter context is the total impact of all the filters that are being applied to a single measure to cause it to calculate in the manner you defined.
A simple measure returns Total Sales ($24,639) for all sales made from the Orders table in the screenshot above.
As you can see, the visual has no filters and shows total sales.
It doess not filter the total sales value by product name or product category, for example.
However, dragging a product name column from the Orders table and dropping it on the visual, automatically filters the Total Sales value measure, and that is one part of the filter context itself.
It’s worth noting that filter context can be defined or applied using the following methods:
- Slicers
- Visual-Level Filters
- Page-Level Filters
- Report-Level Filters
- Visuals (Axis, Legend)
- Drill-through
- Drill-down
- DAX Formulas (CALCULATE)
All the above filter down your data and show different row or column values.
It will be helpful to see some examples of the calculate function in use.
A Simple Calculate Function Example
Our Orders table is above, and the card shows the Total Sales for all the orders in our Orders table.
Let’s try to add up the number of sales made by the Large Orders group, which is a nice task to address with the Calculate functions.
Note: The measure named Total Sales has been created earlier.
Click on the Home (#1) tab, New Measure (#2).
OR from the Fields tab, right-click on the table (#1), and select New Measure (#2).
First, give your measure a name (Total Sales for Large Orders), then add the first parameter which is the expression ([Total Sales]), which we have already defined.
Then the final parameter is the filter context.
We want to sum the sales from the order table if the grouping column is “Large” (Orders [Grouping] = “Large”).
Once you have done that, hit Enter on your keyboard.
The output of the DAX calculate function is shown below.
According to the calculate function, Total Sales for Large Orders total $6,716.
As is shown above, we can check this using the existing filter on the [Total Sales] measure.
More on Filter Context In The Calculate Function
Wait a minute, why do we see the same recurring numbers in a matrix with different categories on the rows?
Shouldn’t the filter contexts for “Small” and “Medium” be distinct in each cell?
Here we’ve defined a measure named “Total Sales for Large Orders”, which evaluates “Total Sales” measures when the Grouping in the Order table equals “Large”.
It is important to note that Calculate modifies and overrules any competing filter context.
In the example above, the “Small”, “Medium” row has a filter context of Grouping = “Small”/ “Medium” (defined by the row label) and Grouping = “Large” (defined by the CALCULATE function).
Because both can’t be true at the same time, the “Small”/”Medium” filter is overridden, and the “Large” (from CALCULATE) takes precedence.
Troubleshooting The Calculate Function.
The most common error message people get when working with the Calculate function is when they are referencing a measure in a Boolean expression.
A function ‘Calculate’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Power BI is just stating that you cannot use a “Measure” in the calculate function’s filter argument.
Conclusion.
Calculate is one of the most versatile tools available in DAX, allowing you to add more depth to you reports.
Mastering it is going to be a key part of developing your DAX skills, so hopefully this article has been helpful.
If you are new to Power BI, you may also want to see these articles:
Introduction to Power BI – What is it? What is it used for?
Introduction to DAX – 3 Minute Article
