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
- Using the VALUES function to amend the query context (this blog)

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 VALUES function to amend the query context

What the **VALUES** function does is keep the existing query
context. To understand what this means (and how and when you would use
it), read on!

## On its own VALUES is pointless

Consider the following measure:

Total qty: =CALCULATE(

SUM('Transaction[Quantity]),

VALUES(Quadrant[QuadrantName])

)

This would calculate the total quantity sold for any pivot table cell, using the same filter for the quadrant as you would anyway! The results would be the same as if you had just summed the quantity:

This is what you would see for the above formula; because you
haven't changed the quadrant filter, it's just the same as if
you'd just used **=SUM('Transaction'[Quantity)**.

## Excluding the South region (1) - not working

Suppose now that you want to exclude the **South** region from
your pivot table (notwithstanding that you could do this using a simple filter
...). The obvious thing to try is this:

Qty without South:=CALCULATE(

SUM('Transaction'[Quantity]),

Quadrant[QuadrantName] <> "South"

)

However, this is what this would show:

All of the quadrant figures are the same.

Here's what's happening. The formula tells Excel to calculate the total sales for the current query context, with the following additional two instructions:

- Remove any existing quadrant constraint.
- Add in a constraint that the quadrant can't be
**South**.

What Excel is showing is the total sales figures for all quadrants except the
**South **one:

If you add the shaded figures you get 252, the total sales figures for amphibians for retail parks excluding the
**South** quadrant. This is what our
incorrect measure is showing.

## Excluding the South region (2) - the correct way

To get this to work, you need to modify the formula so that it keeps the
current quadrant filter, but adds an additional one that the quadrant should not
be **South**:

Qty without South:=CALCULATE(

SUM('Transaction'[Quantity]),

VALUES(Quadrant),

Quadrant[QuadrantName] <> "South"

)

This would then give what we were actually after:

Total sales, excluding the
**South** region. It would have been easier to use a pivot table filter, but you wouldn't have learnt then about the
**VALUES** function!

That's the end of this blog on the **CALCULATE** function, but
you should think of this as the start of your journey into the world of DAX, not
the end!

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