The CALCULATE() function

The CALCULATE() function is the most important function in DAX as it enables the author to modify the filter context under which a measure is evaluated. Regardless of the fields used and filters applied in reports, the filter parameter input(s) to CALCULATE() will be applied. Specifically, the CALCULATE() function will either add a filter to a measure expression (for example, Color = "Red"), ignore the filters from a table or column (for example, ALL(Product)), or update/overwrite the filters applied within a report to the filter parameter specified in CALCULATE().

The syntax of CALCULATE() is the following CALCULATE(<expression>, <filter1>, <filter2>). Any number of filter parameters can be specified including no filter parameters such as CALCULATE(SUM(Sales[Sales Amount])). When multiple filter parameters have been specified, the function will respect all of them together as a single condition via internal AND logic. The expression parameter is evaluated based on the new and final filter context applied via the filter parameters.

In the following measure, any filter applied to any column from the Product or Sales Territory tables will be ignored by the calculation:

ISales Row Count (Ignore Product and Territory) = 
CALCULATE(COUNTROWS('Internet Sales'),ALL('Product'),ALL('Sales Territory'))

The preceding measure represents one simple example of a table function (ALL()) being used in conjunction with a scalar function (COUNTROWS()) via CALCULATE(), as described in the previous session.

There are multiple forms of the ALL() function beyond ALL(table). The ALL() function can be used to ignore the values from a single column or multiple columns, such as, the following two examples: (All('Customer'[Customer City]) and ALL('Customer'[Customer City], 'Customer'[Customer Country]). Additionally, the ALLEXCEPT() function only allows certain columns specified to impact the filter context, and the ALLSELECTED() function ignores filters from inside a query but allows filters from outside the query.

Just as the CALCULATE() function is used to modify the filter context of scalar value expressions, the CALCULATETABLE() function is used to modify the filter context of expressions which return tables. For example, the following query expression returns all columns from the product dimension table and only the rows which match the two filter parameter conditions specified:

EVALUATE
CALCULATETABLE('Product',
'Product'[Product Category] = "Bikes",
'Product'[Product Dealer Price] > 2100)

The modified table result from CALCULATETABLE() can then be used as a parameter input to another table function such as FILTER() or as a filter parameter to CALCULATE().

Table of Contents for The CALCULATE() function

Create new playlist

Sign In

Sign Up

Table of Contents for
The CALCULATE() function