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 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!).
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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]) /
CALCULATE(
SUM('Transaction'[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 |
---|
|
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.