CALCULATE and FILTER Functions in PowerBI


Understanding the CALCULATE function

The DAX function CALCULATE changes the context in which a data expression is evaluated by applying one or more filters, and is often used in measures to perform context-aware calculations.

The syntax is as follows: CALCULATE (<expression>, <filter1>, <filter2>... )

You first define your expression which could be something like "SUM(Profit)" and then add any necessary filters such as "Region = 'West' ".

In the example below we have the following DAX calculation:

Total Profit for Corporate =

CALCULATE (

[Total Profit],

Orders[Segment] = "Corporate"

)

This calculation is essentially saying return the total profit, but only where the segment is corporate. Implementing it into a chart will look something like this:


Incorporating the FILTER function

The CALCULATE function already incorporates some filtering within it, so why is FILTER often used in conjunction with it?

Essentially, CALCULATE does not incorporate row-wise filtering. It can easily filter to fixed values such as Segment = "Corporate", but what if you wanted to filter for where price > 1000? CALCULATE cannot look through each individual row and assess whether the price is higher or lower than 1000. For this, we need to use FILTER.

FILTER will return / generate a new table that is based off a pre-existing table within your dataset, with the values of this new table being what you have filtered down to.

If we look back to the syntax of CALCULATE, we can see that it takes a filter after an expression. If you want to use a FILTER function within a CALCULATE, then it will act as a <filter> in your CALCULATE function (obviously!).

The syntax for this would look like:

Sales for Products Over 1000 =

CALCULATE (

[Total Sales],

FILTER (

Orders,

[Price of Product] > 1000

)

)

Author:
Louis Phipps
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab