564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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!).
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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!
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).
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:
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.