CALCULATE and CALCULATETABLE in DAX:

CALCULATE and CALCULATETABLE are the most important functions in DAX in Power BI. These are the only DAX functions which can change the existing context.

Calculate Function:

Syntax:

CALCULATE (<Expression>,<Filter 1>,<Filter 2>...<FilterN>)

Expression-Mandatory Parameter

Filters- Optional parameters

Key Points about CALCULATE function:

• When we apply a filter to the CALCULATE function, that filter is applied to the whole data model, not just one table/column.

• If CALCULATE is applied in a Row context, Context transition is performed.

• A measure is surrounded by CALCULATE automatically.

CALCULATE with respect to Calculated Columns and Measures in Power BI:

Calculated columns work in a Row context by default. Whenever CALCULATE is applied to a Row Context, it performs context transition i.e. it changes that Row context into an identical Filter context and calculates the given expression in that Filter context.

Let’s see in power BI what happens if we evaluate any expression using CALCULATE in a Calculated Column.

This calculation is from the ‘Supermarket’ dataset downloaded from Kaggle.com.

Here is the Count of Health and Beauty from the Product Line column using CALCULATE function.

Count of Health and Beauty CC = CALCULATE(COUNTROWS('supermarket_sales-Sheet1'),

FILTER('supermarket_sales - Sheet1','supermarket_sales - Sheet1'[Product line]="Health and Beauty"))

Here in this example, we can see that as a calculated column evaluates row by row and when filter is applied as a parameter of calculate, it is converting the row context into the equivalent filter context and giving the result of count of rows of only health and beauty. The result will look like this:

Now let us see what will happen when we calculate measures using the CALCULATE function. Let us calculate the same thing, that is, the count of “Health and beauty” with a measure.

Count of Health and Beauty = CALCULATE(COUNTROWS('supermarket_sales-Sheet1'),

FILTER('supermarket_sales - Sheet1','supermarket_sales - Sheet1'[Product line]="Health and Beauty"))

When we create a measure using CALCULATE, no context transition will happen, as measures by default work under filter context.

Now let us see what will happen when we create a measure in a Row context i.e. introducing an iterator in a measure.

Let's calculate the value of Total “Sports and Travel”

Total Sports and travel QYT = CALCULATE(SUMX('supermarket_sales - Sheet1','supermarket_sales - Sheet1'[Unit price]

*'supermarket_sales - Sheet1'[Quantity]),'supermarket_sales - Sheet1'[Branch]="A")

The Result, when put in a card visual will be:

Steps performed by CALCULATE:

Calculatetable Function

It works the same as CALCULATE, except it returns a table as a result.

SYNTAX:

CALCULATETABLE (

<TABLE>,<Filter1>,<Filter2>,...<FilterN>

In our database, there are two types of customers

1. Member

2. Normal

Let's create a sub table with ‘Members’ Only.

It will give the following sample table as a result.

CALCULATE and CALCULATETABLE in DAX are two important functions. CALCULATE returns the scalar value as a result, while CALCULATETABLE returns a table as a result.

CALCULATE / CALCULATETABLE + Row context= Context Transition/ changed to equivalent Filter context.

Thanks for reading!