So far, we have only looked at implicit filter context, a context created when you use filters or slicers on a Power BI report or add rows and columns to a PivotTable in Excel. However, it is also possible to create an explicit filter context using the DAX CALCULATE function.
The CALCULATE function in DAX evaluates an expression, as an argument, with a context that is modified by the filters that are passed in one or more additional arguments to the function. It is possibly the most important and complex function in the whole of the DAX language. Although it appears very simple when you first look at it, how it can be used and how it can alter an existing filter context can quickly become confusing.
While other functions can remove either part or all of an existing filter context, the CALCULATE function, along with the associated CALCULATETABLE function, are unique in DAX in that they are the only functions that can alter the context. It is this ability that makes them so powerful and so useful to you as a BI professional.
The following is the syntax of the CALCULATE function:
CALCULATE ( <expression>, <filter1>, <filter2>, … )
The function has only one mandatory argument: the expression that is to be evaluated. It will then take one to many optional filter arguments. These optional filter arguments are combined to form the overall filter, which is applied to the expression given as the first argument.
Some restrictions apply to Boolean expressions used as arguments:
- Expressions cannot reference a measure.
- Expressions cannot use a nested CALCULATE function.
- Expressions cannot use any function that scans a table or returns a table, including aggregation functions.
However, expressions can use functions that look up single values or calculate a scalar value.
The power of the CALCULATE function comes from its ability to alter the existing filter context of the expression passed in the first argument, by the n number of filter conditions specified by the following arguments. This is done according to the following:
- If the filter context specified by a filter condition already exists, it will override the existing filter context with the new one specified in the expression.
- If the filter context does not exist at all, it will add a new one according to the filter conditions specified.
As you can see, the syntax for the CALCULATE function is straightforward but following what it is doing is more complex. The best way to show this is through a hands-on example.
In the following example, we have what is possibly the most common scenario for using the CALCULATE function, which is to take a value and calculate what percentage it is of an overall total.
Let's start by creating a new measure to calculate the sum of a column called SaleQuantity in a table called Sales, by using the following DAX expression:
SumOfSalesQuantity =
SUM ( Sales[SalesQuantity] )
In the screenshot shown in Figure 1-19, the measure has been added to a table in Power BI, along with the manufacturer. The manufacturer becomes the filter context for the measure, giving a breakdown of sales quantity by manufacturer:
Now, to be able to calculate the sales quantity of each manufacturer as a percentage of the overall sales quantity, each row will need to know what the overall sales quantity is. To do this, you need an expression that will amend the filter context by removing the manufacturer from the filter. This is where the CALCULATE function comes in.
The next step is to create another measure, which again will calculate the sum of the SalesQuantity column, but uses the ALL function to amend the current filter context:
TotalSalesQuantity =
CALCULATE (
SUM ( Sales[SalesQuantity] ),
ALL ( 'Product'[Manufacturer] )
)
In this code, we see the following:
- The first argument calculates the total sum of values in the SalesQuantity column of the Sales table.
- The next argument, the first filter argument, will effectively amend the current filter context by using the ALL function to remove any existing filters on the Manufacturer column of the Product table.
Figure 1-20 shows this measure added to the Power BI table:
As you can see, for each row, the filter context has been altered by the TotalSalesQuantity measure and returns the overall sales quantity, regardless of the manufacturer.
With these two new measures, it is possible to create a measure to calculate the sales quantity of each manufacturer as a percentage of the overall sales quantity:
%SalesQuantity =
DIVIDE (
// The sum of sales quantity measure - current filter context
[SumOfSalesQuantity],
// The sum of sales quantity measure - current filter context altered
// to include ALL manufacturers
[TotalSalesQuantity]
)
In this example, we use the DIVIDE function. This function divides the value returned by the measure passed as the first argument (the numerator), by the value returned by the measure passed as the second argument (the denominator). The DIVIDE function also allows for an optional third argument that specifies the alternative value to be returned when division by zero results in an error. When this third argument is not provided, as in this example, the default alternative of BLANK is returned.
Figure 1-21 shows this percentage measure added to the Power BI table:
Finally, it's possible to rewrite this measure as a self-contained measure that doesn't require the intermediate measures of SumOfSalesQuantity and TotalSalesQuantity.
Let's have a look at the following example, which demonstrates this:
%SalesQuantity2 =
DIVIDE (
// The sum of sales quantity - current filter context
SUM ( Sales[SalesQuantity] ),
// The sum of sales quantity - current filter context altered
// to include ALL manufacturers
CALCULATE (
SUM ( Sales[SalesQuantity] ),
ALL ( 'Product'[Manufacturer] )
)
)
This is a relatively simple example of the CALCULATE function being used. In Chapter 5, Getting it into Context, there will be some more complex examples when we look at evaluation contexts in more detail.
