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)