 How To Differ CALCULATETABLE Over FILTER In DAX — DAX in Power BI — Chapter 10

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()

1. Click in the Data section from the left panel.
2. 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

1. 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.

Please find the code in the below location

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

Conclusion

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