DAX-Lesson 2. CALCULATE function

The first function with which you should start learning DAX is CALCULATE. This function does not have an exact analogue in Excel, but it can be compared with the functions SUMIFS and COUNTIFS. In short, this function applies filters to other calculation functions, thus we can select and include in calculations only the data we need.

CALCULATE() Function Syntax

     =CALCULATE(<statistical expression>, <filter1>, <filter2>, )

<statistical expression>

Statistical expression should return us the result of the calculation (a value, not a table). For this reason, statistical functions such as SUM, MIN, MAX, COUNTROWS, etc. are often used in this parameter.

1. SUM([ColumnName])
2. SUM([ColumnName1]) / MAX([ColumnName2])
3. Name of another calculation field (measure)

<filter1>, <filter2>, …

Filter defines the range of data to be worked on. Conditions for selecting values are specified here.

1. [ColumnName] = “Auto”
2. [ColumnName] >= 6
3. ALL('TableName') or ALL('Table Name'[Column Name])
4. FILTER('TableName', 'TableName' [Column Name] = "Green")

Suppose we have the following table called Demo and loaded into PowerPivot.

Let's create a summary table based on it and count the number of records for each product.

Now let's create a new calculated field (measure) Count_All_Product in our pivot table that will display the total number of records across all rows.

How did we get 16 numbers in the calculation field Count_All_Product? The explanation is that we have removed for this field all the filters that were applied in the pivot table (each row in the pivot table is a separate filter) using the ALL() function. That is, in other words, the function ALL (Demo) gives the command to remove all filters for the calculation field Count_All_Product that are applied to the table Demo.

Let's add another field to the rows to see the sales of each product by city.

We see that the numbers in the Count of Rows field change, but not in the calculation field Count_All_Product. That's correct, because for the last one we removed all the filters in the formula. Now let's make some minor changes to our formula and see the result.

=CALCULATE(COUNTROWS(Demo);ALL(Demo[Product]))

We changed the argument to the ALL() function, replacing Demo with Demo[Product]. Filters will now be removed not for the entire Demo table, but only for its Demo column [Product].

So we see that the filters Product and City act on the field Count of Rows, and on the calculation field Count_All_Product only filter City.

Related Articles: