DAX CALCULATE (The KING) OF ALL!! (2023)

POWER BI DAX: CALCULATE

Hey guys Llew here from Databear.

Welcome back to yet another DAX Data Bear necessity. If you missed last week’s video/blog please go to the following LINK to find the first of many DAX videos of our Data Bear necessities series, an introduction to SUM, SUMX and CALCULATE.

This week will be building on that knowledge and taking us just one step closer to becoming DAX Jedi’s. Let’s take a closer look at CALCULATE. CALCULATE is probably the most important DAX function you’ll ever learn and is also the only function in DAX where you can change the filter context.

So, let’s jump right in and see what this function has in store for us.

If you prefer to watch the YouTube video, rather than read the blog, please see the video below:

DAX CALCULATE INTRODUCTION

Let’s jump in deeper to see how this works.

To start off let’s do our first calculation which will act as the base calculation for our Sales measures. For this measure we are going to do the sum of the sales table from the total sales column.

Let’s format the measure to British pound.

Let’s use a matrix visual and show the ‘Sales’ by CUSTOMERNAME. Add CUSTOMERNAME as the row value and ‘Sales’ as the values.

DAX FILTER CONTEXT

Let’s have a look at how CALCULATE works. If we look at how CALCULATE filters are evaluated, it first looks at the outer filter context before applying the inner filter context (inside the CALCULATE – referred to as inner filter context) and then continues to calculate the DAX expression.

Using the matrix example above, we know that irrespective of the filter context from the measure itself, the actual CUSTOMERNAMEs will also apply an outer filter context by showing us sales values filtered by each CUSTOMERNAME.

Let’s do another measure. I want to do the sales amount where the sales quantity is greater than 100. For ease of reference, I’m going to call it ‘Sales quantity > than 100’.

I want to do a CALCULATE of the sales measure we created earlier called ‘Sales’ where the sales quantity is greater than 100.

Let’s add the measure to our canvas. We can see that the values of the ‘Sales Quantity > 100’ measure is less than the original total ‘Sales’ measure values.

The reason for that is that our new ‘Sales Quantity > 100’ measure has filtered the sales quantity on the inside of the evaluation, but it’s done so after it’s applied the outer filter context of each CUSTOMERNAMEs.

If we look at the Sales table from the Data view pane, we can see what it has done is to filter on the CUSTOMERNAME for each row in the table (ACME Plumbing for example), which is the outer filter context.

Then it goes to the inner filter context (inside the DAX CALCULATE function) where it takes the quantity column and applies a filter where the sales quantity is greater than 100.

It then sums up the total sales together to give us an answer for each of the CUSTOMERNAMEs.

Let’s explore this a bit further…. Let’s create another measure.

I want to do a similar calculation than before where I’m going to calculate the total sales where the quantity again is greater than 100. But this time I’m going to use SUMX instead of SUM, so let’s see how that works.

Start with CALCULATE and use a SUMX of the ‘Sales’ table and multiply the Sales[Unit Price] by the Sales[QTYNET] (the Quantity) and then finally let’s include a filter where the Sales[QTYNET] > 100. Format to British Pound and let’s put it on the canvas.

Okay great so it seems like it does the same thing, right?! We can see that the answer from our SUM and from the SUMX is the same. The difference with SUMX and SUM is in the way it evaluates.

SUMX will go row by row and will start with the Sales[QTYNET] filter.

So for each row where it finds a quantity larger than 100, it will calculate the Sales[Unit Price] * Sales[QTYNET] and store that value, and once it has reached the bottom of our table, it will SUM all those values together and return the answer.

That’s the key difference between SUM and SUMX. If you want to review that please go and have a look at the last week’s video where we looked at SUM, SUMX and CALCULATE.

Let’s do another few measures just to explain how calculate works.

A quick note on adding new measures: Do you know that the table that you last selected or clicked on is where a new measure gets added when you click on the ‘New Measure’ button? If you want to easily place that measure in a specific group, you can just right click on the table or place holder and then select, ‘New Measure’.

CALCULATE AND OTHER FUNCTIONS

Back to our measure, let’s do another measure where the sales quantity is greater than 100, but this time I want to add a few other interesting functions into our calculation.

Let’s do a CALCULATE, where we SUM the Sales[Total Sales] but in this case let’s add a FILTER function and the ALL function.

So we’re gonna FILTER ALL values from the Sales table where the Sales[QTYNET] is greater than 100. Let’s call the measure Sales Quantity > 100 equiv (for equivalent of the previous measure).

The FILTER function as we know will return a filtered table, and the ALL function will force ignore all outside filter context and apply the expression on ALL items where Sales[QTYNET] is greater than 100.

If we add the measure to our canvas, we can see it returns the same answer still. If we look at our two measures we have the first one which is just a normal CALCULATE where we added a filter where the quantity is larger than 100.

For our second measure, we used we did the same thing but instead of SUM we used SUMX to get us the same value and in the last one we’ve now said CALCULATE the SUM of Sales[Total Sales] but before we do that FILTER the Sales table and return ALL values irrespective of our outside filter context where it will force the the calculation to return the entire table and then filter it where the Sales quantity is greater than 100.

Okay great so so we have three separate calculations which are different yet provides us the exact same answer and and they get to that answer in slightly different way. Let’s use another few examples and that I want to use just to explain how and these other calculations will work if we actually apply an outer filter context.

For the first measure I want to do the total sales for the customer called “The Courtyard”.

So I want to filter all cells with a customer name that is equal to “The Courtyard”.

Start with a SUMX and add the FILTER function of the Sales table and then also apply the RELATED function where it fetches the value from a related table (Called Customers) and I want to go and find the customer name from customers table where it’s equal to “The Courtyard”.

Finally we want to be able to do the Sales[Unit Price] multiplied by the Sales[QTYNET].

Drag it onto the canvas and we can see that now it has returned a value, but only for “The Courtyard”.

Note: Remember that SUMX is an iterator, so it goes through each line in the table and CALCULATES the Sales[Unit Price] multiplied by the Sales[QTYNET], but before it does so it finds the “The Courtyard” value from the RELATED Customers table, and applies the FILTER in the CUSTOMERNAME column.

Note that RELATED can only work if there is a relationship between the Sales and Customers table.

Let’s do a another measure, but this time we’ll use CALCULATE with the [Sales] measure we already created previously.

Remember that when we use CALCULATE we can either do a SUM of the Sales[Total Sales] or use an existing measure called [Sales] which already calculates a SUM(Sales[Total Sales].

To continue with our formula, let’s CALCULATE the [Sales] and we want to filter the CUSTOMERNAME from the Customers table where the value is equal to “The Courtyard”.Finally lets formatted to British Pound and add it to our canvas.

It seems like it’s given the same answer as with our previously used RELATED function inside of the Sales (The Courtyard) measure.

Before we test adding an outer filter context, I would like to add one more measure.

So as an introduction to the ALL and FILTER functions and how they work with the CALCULATE funtion, lets create a measure called ‘Sales (The Courtyard) CALCULATE_FILTER_ALL’.

For this measure I want to CALCULATE again of the [Sales] measure, lets use the FILTER function of ALL items in the Sales[CUSTOMERNAME] column and finally filter the Sales[CUSTOMERNAME] table to only include items that belong to “The Courtyard”.

So before it CALCULATEs the [Sales] measure, it first applies the FILTER context. It starts with the FILTER function, returns ALL values from the Sales[CUSTOMERNAME] column (This actually returns a unique list of the values from the Sales[CUSTOMERNAME] column) and then filters the column values to only include “The Courtyard”.

Then it CALCULATES the [Sales] measure and returns the answer. Again lets add it to the canvas and format to British Pound.

Interesting, so it returns a value for every single customer but which is equal to “The Courtyard”. In this case it actually forces it with ALL and the internal filter context to ignore the outer filter context.

Let’s test this with a slicer. Let’s add add CUSTOMERNAME as a slicer and let’s see what happens. Lets select ‘ACME Plumbing’.

So it seems it has only returned the original [Sales] measure and the measure where we forced the filter context for “The Courtyard”. If we choose another CUSTOMERNAME it will only return the value for the [Sales] and the sales value for “The Courtyard”.

If we choose “The Courtyard” from the CUSTOMERNAME slicer, we will see a value for every measure.

CONCLUSION

And that’s it for this week. I hope you’re starting to understand how the CALCULATE function works.

It really does get a lot easier with practice and we’ll go into a lot more detail in the coming blogs / videos. But for now just know that for CALCULATE first evaluates the filter context of the outer, then the inner and finally calculates the expression.

Thank you so much for joining me this week.

For more posts from us, go here. Attend our training, to learn even more.

Until next time keep safe and stay blessed!!