TOP
# DAX-Lesson 2. CALCULATE function

## CALCULATE() Function Syntax

### Related Articles:

DAX-Lesson 1. What is DAX. Basic concepts.
DAX-Lesson 3. FILTER data selection 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

=**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.

**SUM([ColumnName])****SUM([ColumnName1]) / MAX([ColumnName2])**- 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.

- [
**ColumnName**] = “*Auto*” - [
**ColumnName**] >=*6* **ALL(****'TableName'****)**or**ALL(****'Table Name'**[**Column Name**]**)****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**.

- 1. What is VBA, basic concepts.
- 2. Code Debugger
- 3. Working with worksheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties
- 6.1. Data types (Variables)
- 6.2. Data types (Continued)
- 7.1. Conditions
- 7.2. Conditions (Continued)
- 8.1. Loops
- 8.2. Loops (Continued)
- 9. Procedures and functions
- 10. Dialog boxes (Dialog boxes)
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls
- 12.3. Controls (Continued)
- 12.4. Controls (Exercises)
- 13.1. Arrays
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. What is SQL? SQL essentials.
- 2. Select data from a database (SELECT)
- 3. Sorting the result records (ORDER BY)
- 4. Filter records (WHERE)
- 5. Using wildcards (LIKE)
- 6. Computed (calculated) columns
- 7. Aggregate functions (MIN, MAX, AVG etc.)
- 8. Grouping of the resulting set (GROUP BY)
- 9. Subqueries
- 10. Combining records from two tables (INNER JOIN)
- 11. Outer join (LEFT, RIGHT, FULL JOIN)
- 12. Combining the resulting sets (UNION)
- 13. Insert new records (INSERT INTO)
- 14. Create a new table (CREATE TABLE)