### CALCULATETABLE is more applicable for context transition requirement.

DAX has a different set of functions to manage tables. In this blog, we are going to explore CALCULATETABLE() function. This function helps create and manage tables.

Based on **Microsoft **documentation, the **CALCULATETABLE **function evaluates a **table expression **in a **modified filter **context.

CALCULATETABLE is the same as CALCULATE function, the difference is in their output. **CALCULATETABLE **returns a **table **whereas **CALCULATE **returns a single **value **like an integer or a string.

It’s time to understand deeply with some example.

## Get Data

For this case study, I consider the US Superstore dataset from Kaggle.

I have considered the code from one of my **case study** (How to Create Dimensional Queries and Decomposition Tree).

If you want to get that file, please check the GitHub (US_Superstore_Decomposition Tree project) location.

## Syntax of CALCULATETABLE()

Syntax is following

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

As per Microsoft documentation, the parameters are following.

## How to use CALCULATETABLE()

- Click in the
**Data**section from the**left**panel. - Go to the
**Table Tools**tab and click on the**New table**under the**Calculations**group.

3. Write a DAX formula using CALCULATETABLE and create a new table with the filtered condition.

4. The newly created table will have the same structure as the Product table with filtered values.

## Difference between CALCULATETABLE and FILTER function

- In the above CALCULATETABLE formula, if you just replace it with the FILTER function, a table will generate the same number of rows.

2. But there is a difference between the CALCULATETABLE and FILTER function in terms of formula interpretation.

3. CALCULATETABLE first work on filter context, then evaluate the expression.

4. On the other hand, FILTER iterates the results of the first expression and it is not working on the filter context.

5. In the below example you can observe the difference.

6.You can verify the number of products count with the product table using the above-mentioned filter condition.

7. Now you can visualize that how the FILTER function is not working in the filter context. If you want to use the FILTER function, then CALCULATE function will help to solve the problem. Find the expected result below.

8. In the above code, first you are using the ADDCOLUMNS function. Within that the FILTER runs first and then it converts filtered rows for COUNTROWS using CALCULATE.

## What is the purpose of the CALCULATETABLE?

I read different books and materials to understand the purpose of this function. There have certain advantages and disadvantages.

You can use **CALCULATETABLE**, whenever you want to **apply **a **filter **to a **model column **and there is a requirement for **context transition** or filter context modifiers.

It means, **CALCULATETABLE can **be applied to a **column **that is present in the data model. It **can’t **be applied to a **measure **like the FILTER function.

As you can use different filter context modifiers like USERELATIONSHIPS, CROSSFILTER and others (check Microsoft Documentation for details) and it helps to perform context transition. Therefore, the CALCULATETABLE function becomes more **powerful **than FILTER.

Based on the requirement scenario, we have to decide which function to use.

## Download

Please find the code in the below location

DAX — Chapter 10https://github.com/arpitag1/Power-BI

## Video

## Conclusion

In this blog, we learn how the CALCULATETABLE function differs from the FILTER function in DAX.

If you have any questions related to this project, please feel free to post your comments.

Please visit my website for other technical resources.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

## 1 comment