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