DAX-Lesson 2. CALCULATE function

Description

The first function to start learning DAX is CALCULATE. This function does not have an exact counterpart in Excel, but it is comparable to 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.

Syntax of function CALCULATE()

=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 of the type are often used in this setting SUM , MIN , MAX , COUNTROWS etc.

1. SUM([ Column name ])
2. SUM([ NameColumns1 ]) / MAX([ NameColumns2 ])
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. [ Column name ] = Auto
2. [ Column name ] >= 6
3. ALL( 'TableName' ) or ALL( 'TableName' [ Column name ] )
4. FILTER ( 'TableName' , 'TableName' [ Column name ] = " Green ")

Suppose we have such a table, which is called Demo and loaded in 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 calculation field (Measure) Count_All_Product in our pivot table, which will display the total number of records against all rows.

How did we get the numbers in the calculation field Count_All_Product 16 ? The explanation is that we 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 function ALL() . That is, in other words, a function ALL (Demo) gives the command to remove all filters for the calculated field Count_All_Product that are applied to the table Demo .

Let's add another field (City) in rows to see the sales of each product by city:

We see that the numbers are in the field Count of Rows 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 by replacing Demo on Demo[Product] . Now the filters will not be removed for the entire table Demo , but only for its column Demo[Product] .

So we see what is on the field Count of Rows filters are in effect Product and City , and only a filter on the calculated field Count_All_Product City .