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
How to use the CALCULATE function in DAX measure formulae
Part three 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 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.
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.
Here is a formula which would give the required results:
Fraction of South:=SUM([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 ...
|Parts of this blog|
25 Aytoun Street