This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to use the CALCULATE function in DAX measure formulae Part four 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!). 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.

How to use the CALCULATE function in DAX measure formulae

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

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

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!

Parts of this blog
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)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC