# CALCULATETABLE DAX Function – Best Practices in Power BI

The CALCULATETABLE DAX function is an incredibly advanced and important function in Power BI to learn and understand well. You may watch the full video of this tutorial at the bottom of this blog.

Most of you who are just starting out with Power BI have probably overlooked this particular function. I certainly did when I first started out using Power BI and writing DAX measures.

Learning about CALCULATETABLE well is the beginning of many advanced calculations that you can do in Power BI.

Itâ€™s quite a complex function to understand and actually implement in Power BI. But over time, Iâ€™ve discovered how great it is in several scenarios and demos Iâ€™ve worked through. I now have a clear understanding of how and when to use it in different ways. Thatâ€™s what I want to share with you in this tutorial.

## The Best Time To Incorporate The CALCULATETABLE Function

One of the best times to incorporate the use of the CALCULATETABLE DAX function is when you’re trying to analyze your churn analytics.

Churn analytics involves the evaluation of a company’s customer loss rate. Finding out this data using Power BI can help a lot in terms of assessing your products. Through this way, you can speed up your marketing efforts to reduce customer loss.

The table above shows a comparison of new and total customers for a specific month and year. It involves the data of new, lost, and total customers.

The key to extracting these important insights is through the use of the CALCULATETABLE DAX function.

## Using CALCULATETABLE DAX To Find Out About New Customers

Firstly, I’ll show you the formula to calculate new customers using CALCULATETABLE

Here, we need to compare the current customer set from a customer set of a prior period. I only consider customers as new, if they have purchased now but havenâ€™t done so in the past 90 days.

To be able to find that insight, this is where I integrated the CALCULATETABLE function. Take note that I used this together with other table functions, just like the EXCEPT function.Â

The EXCEPT function evaluates two tables and returns the customers that are in the first table, but not on the second one.Â Next, I wrapped it inside the COUNTROWS function to really work out the needed calculation.

Looking back to the main point. The CALCULATETABLE function enables us to open a window in any particular context. In my example, it looks back 90 days to find a customer set.

Since we donâ€™t want to look at the customers in the current context, but look at the customers over the previous 90 days, we now use the FILTER function.

Then, Iâ€™ve placed it inside another table function and do some follow-up evaluations.

To sum up, the perfect way to use CALCULATETABLE is to change the context of a table evaluation. Thatâ€™s generally how you should use it. But then, you can also incorporate other formulas that you can use to compare tables like EXCEPT andÂ  INTERSECT.

## Using CALCULATETABLE DAX To Look For Lost Customers

Aside from digging into your new customer analysis using the CALCULATETABLE DAX function you can even get more advanced when you utilize it for finding out your lost customers.

Take a look at this formula for finding out lost customers.

I have actually discussed the full logic of this formula in another blog. But then again, weâ€™re just doing a similar calculation here for lost customers.

If you look at the formulas for CustomersPurchased and PriorCustomers, weâ€™re using CALCULATETABLE.

Furthermore, you can still find the EXCEPT function. But this time, it compares the tables of CustomersPurchased and PriorCustomers.

In this case,  we are changing the period of evaluation of these individual tables based on a different period. This just shows a more advanced way to polish how we show new customers and lost customers.

If you use Power BI soon, youâ€™ll understand that this is an intensive analysis. Moreover, we can make the formulas more intuitive especially when you use variables as well.

Just think about what you put inside the first parameter in CALCULATETABLE. It’s usually a table function, and then you change the context of that table evaluation. From there, you can put it inside another table function and see how things evaluate from there.

## Conclusion

I know CALCULATETABLE is a bit hard to understand if youâ€™re just starting out. But thatâ€™s the main reason why I created this tutorial for you.

After you understand and discover the best practices for using it, you can start completing advanced calculations and analysis from your raw data.

By reviewing it in this way, I believe youâ€™ll have a better understanding of how you can utilize the CALCULATETABLE DAX function yourself in your own reports and models.

Don’t forget to subscribe to Enterprise DNA TV to get plenty of other content.

Good luck with learning this one.

Sam

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...