DAX – CALCULATE Function

CALCULATE DAX  function evaluates an expression in a modified filter context. It comes under Filter DAX function category.

Syntax:

CALCULATE( <expression>, <filter1>, <filter2>… )

Description:

Note:

1. CALCULATE is the most often used DAX function in Power BI,  this function works as a base function to apply other DAX functions in different scenarios.
2. It evaluates the expression given by the user with all the applied filters.
3. First argument(expression) returns a value not table, so you can use aggregation function like SUM, MIN, MAX, AVERAGE, COUNTROWS and so on.

So, Let’s start with an example,  you can download the sample Dataset from below link:-

• SuperStoreUS-2015.xlxs

Examples:-

Suppose you want to calculate sum of sales for particular Product category, so for this you can use SUMX function because SUMX function support two argument, in first argument you can pass filter condition and in second argument you can pass sales column name.

SUMX with filter =

SUMX(

Filter(Orders, Orders[Product Category]="Furniture"),

Orders[Sales])

However, if you intend to achieve the same task using the “SUM” function, how would you proceed?

Here challenge is that, SUM function only support single argument that is column name.

To overcome this limitation, you can use CALCULATE function with SUM function, as you know it support two argument, expression & filter.

In first argument (expression) you can use aggregation function like sum and in second argument you can use Filter condition.

SUM Measure=

CALCULATE(

SUM(Orders[Sales]),----Expression

Filter(Orders, Orders[Product Category]="Furniture")-- filter

)

Output : 660,704,31.

Some other examples of CALCULATE functions:

COUNTROWS = 

CALCULATE(
COUNTROWS(Orders),
FILTER(Orders, Orders[Region]="East")

)

Output : It will return no of rows under East region.

Distinct Count  =

CALCULATE(

DISTINCTCOUNT( Orders[Product Category]),

Filter(Orders, Orders[Region]="EAST" )

)

Calculate with Multiple filter =

CALCULATE(

SUM(Orders[Sales]),----Expression

Filter(Orders, Orders[Product Category]="Furniture"),-- Filter1

Filter(Orders, Orders[Region]="West")-- Filter 2

)