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.

Image by Microsoft

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.
Image by Author

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.

Image by Author

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.
Image by Author

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.

Image by Author — Using CALCULATETABLE Formula

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

Image by Author — Using 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.

Image by Author — Using FILTER and CALCULATE

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: