CALCULATE & CALCULATETABLE - What's The Real Diffe...

EnterpriseDNA · ‎07-22-2019

I want to dive into two essential DAX functions that you absolutely need to understand well when using Power BI. These functions are CALCULATE and CALCULATETABLE. They are very similar in some aspects but also very different in others. I want to go through these with the accompanying tutorials during this post.

The essential thing to know about these particular functions is that they can change the context of a calculation. The output that they produce can be very different.

What CALCULATE does is it returns what is deemed a scalar value. This is essentially a singular result or value that you can have within a table or Power BI visual.

Within CALCULATE you can adjust the context in many different ways. You can use simple filters or you can use more advanced table filters that enable you to adjust the context virtually in a range of different ways.

In the below example I go through some of these more simple filters with CALCULATE and how you can implement these within Power BI.

Also below is a good review of the CALCULATE function in general. How you need to think about and use CALCULATE within Power BI, especially when you're starting to use DAX Measures.

One of the key things to note within CALCULATE is we want to first reference a measure as the first parameter (this isn't a rule but certainly a best practise in my view).

Then, once you have done that, you can apply the additional context within the filter parameters of CALCULATE itself.

For some further links with ideas around using the CALCULATE function check out the below....

Create Dynamic Cumulative Totals Using DAX In Power BI

Cumulatively Compare Information Over Different Months

ALL Function in Power BI – How To Use It With DAX

Now I want to touch on CALCULATETABLE. This function can return a virtual table or a physical table, depending on how you utilise it. In general, if you're using CALCULATETABLE within a DAX measure, it is going to return a table virtually for you.

What you can do with this is you can change the context of a calculation or a virtual table within CALCULATETABLE.

To be honest, the CALCULATETABLE is the most difficult function in DAX to learn when you're just starting out. Though this is purely from personal experience. It took me some time to realise when and why you'd actually use this.

In this below example I show you a really good example of where this can be used and how it can be used effectively when trying to solve some scenario or insights inside of Power BI.

I think this is actually the best example that I could come up with in terms of learning what CALCULATETABLE actually does and how you can apply it effectively.

Here's some further example of when this particular function has been exampled previously

Evaluating New Customer Sales - Advanced Analytics in Power BI

How To Use Virtual Tables With COUNTROWS in Power BI - DAX Formula Technique

Enjoy working through these tutorials.

These are really good things to review and learn from especially if you are just starting out with DAX and looking to master some more intermediate concepts.

All the best.

Sam

**** Learning Power BI? ****

FREE COURSE - Ultimate Beginners Guide To Power BI

FREE COURSE - Ultimate Beginners Guide To DAX

FREE - 60 Page DAX Reference Guide Download

FREE - Power BI Resources

Enterprise DNA Membership

Enterprise DNA Online

Enterprise DNA Events

****Related Links****

How To Understand Virtual Tables Inside Iterating Functions In Power BI – DAX Concepts