BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

- The CALCULATE function in DAX measures
- Removing filters in an expression using CALCULATE
- Using the CALCULATE function to replace filters (this blog)
- 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:

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

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:

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

- The CALCULATE function in DAX measures
- Removing filters in an expression using CALCULATE
- Using the CALCULATE function to replace filters (this blog)
- Using the VALUES function to amend the query context