How to use the CALCULATE function in DAX measure formulae
Part three 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
  2. Removing filters in an expression using CALCULATE
  3. Using the CALCULATE function to replace filters (this blog)
  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.

Using the CALCULATE function to replace filters

The previous part of this blog showed how to remove filters altogether from the query context; this part shows how to keep a filter, but change it.

Our worked example - sales as a percentage of Southern sales

Suppose we want to show the following pivot table:

Sales as % of south

We're showing sales for any cell as a percentage of the equivalent sales for the South region. The South column necessarily always equals 100%.

A quick reminder of the underlying sales figures:

The sales figures

The 34.82% for the East quadrant shown shaded in the pivot table at the top of this page equals 39 (the figure for East quadrant amphibian sales for Retail Park centres) divided by 112 (the figure for South quadrant amphibian sales for Retail Park centres).

Tuple-wise (strangely, my spell-checker wasn't keen on that word) we're showing for the shaded cell the results of this calculation:

Tuple to use

The tuple shows we're changing, not removing, the quadrant filter.

 

The formula to use

Here is a formula which would give the required results:

Fraction of South:=SUM([Quantity]) /

CALCULATE(

SUM('Transaction'[Quantity]),

'Quadrant'[QuadrantName] = "South"

)

The important thing to realise is that by including a filter for the quadrant, it replaces whatever previous quadrant constraint was imposed by the pivot table cell's query context with this one:

'Quadrant'[QuadrantName] = "South"

Adding a filter to a dimension for the CALCULATE function thus destroys the previous query context for that dimension.

The obvious question to ask is then: what happens if you want to preserve the existing query context, but adapt it?  Enter the VALUES function ...

This blog has 0 threads Add post