Use CALCULATE to manipulate filters

Earlier in this chapter, you saw that the CALCULATE function can be used to alter relationships when paired with other DAX measures. The USERELATIONSHIP function with CALCULATE can activate inactive relationships, and CROSSFILTER with CALCULATE can change the filter direction.

The CALCULATE function also allows you to alter the filter context under which measures are evaluated; you can add, remove, or update filters, or you can trigger context transition. We cover row context, filter context, and context transition in more detail later in this chapter.

CALCULATE accepts a scalar expression as its first parameter, and subsequent parameters are filter arguments. Using CALCULATE with no filter arguments is only useful for context transition.

Adding filters

CALCULATE allows you to add filters in several formats. To calculate profit for the New England sales territory, you can write a measure that you can read as “Calculate the Total Profit where the Sales Territory is New England”:

Click here to view code image

New England Profit =
CALCULATE(
    [Total Profit],
    City[Sales Territory] = “New England”
)

Importantly, you’re not limited to using one value per filter. You can calculate profit for New England, Far West, and Plains:

Click here to view code image

New England, Far West, and Plains Profit =
CALCULATE(
    [Total Profit],
    City[Sales Territory] IN {“New England”, “Far West”, “Plains”}
)

You can specify filters for different columns at once too, which are combined by using the AND DAX function. For example, you can calculate profit in New England in 2020 that reads as “Calculate the Total Profit where the Sales Territory is New England and the Year is 2020”:

Click here to view code image

New England Profit 2020 =
CALCULATE(
    [Total Profit],
    City[Sales Territory] = “New England”,
    ‘Date'[Year] = 2020
)