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. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"