WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to use the CALCULATE function in DAX measure formulae
Part one of a four-part series of blogs

The CALCULATE function is the most important one in DAX. This blog shows you how to use it to replace, remove and amend the query context for a measure (and also explains what this sentence means!).

1. The CALCULATE function in DAX measures (this blog)
2. Removing filters in an expression using CALCULATE
3. Using the CALCULATE function to replace filters
4. Using the VALUES function to amend the query context

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The CALCULATE function in DAX measures

The CALCULATE function is the heart and soul of DAX.  You should read this blog carefully to make sure you understand it!

The only real way to understand the CALCULATE function is by using worked examples - which is what this blog presents.

The model we'll use

This blog uses the following model (it's the one I used in the previous blog on basic measures):

The model used for this blog.

The syntax of the CALCULATE function

Here's what the CALCULATE function looks like:

CALCULATE ( Expression, Filter 1, Filter 2, ... )

Here's what the Microsoft website has to say about it:

The function calculates an expression, but with modified query context.  I'll spend the rest of this blog explaining what this means!

Using CALCULATE with only one argument

You don't have to modify the query context (whatever that means ...).  Here's an example of using CALCULATE pointlessly:

We could have just written this as =SUM('Transaction'[Quantity]), to do exactly the same thing!

This blog will show how to:

• remove filters;
• replace filters; and
• change filters.

We'll start with the first item in this list: using CALCULATE to remove filters from a formula.  All will become clear!