This blog post is a review of The Definitive Guide to DAX by SQLBI. There's also a companion blog reviewing the Mastering DAX Workshop.
Summary
The Definitive Guide to DAX is truly a definitive guide to DAX - a title well named. The book covers the very most basics of DAX all the way up to advanced level DAX in 20 chapters. This means it covers things like row and filter contexts, time intelligence, calculation groups, and my favorite, optimization. Even though the book is centered around DAX, it reaches out to other concepts like compression methods which is a nice touch. After reading this book, you will have a solid understanding of DAX.
Key Takeaways
I highly encourage you to pick up the book for yourself, as a blog post such as this does not do it justice. The authors Marco and Alberto are quite thorough in their explanations and give numerous tricky examples. And I think that's the best part of the book - explanations of tricky, seemingly simple DAX code that goes awry in other contexts. For example, the introduction of a Snowflake schema can have a surprisingly big impact on your model measures if you do not know what you are doing (the reason is due to how expanded tables work with filter propagation).
The filter context filters, the row context iterates, they are not the same.
With a somewhat cheeky attitude they claim this is the DAX programmers mantra, and I love it. "The filter context filters, the row context iterates, they are not the same." I think this concept can be expressed with two DAX examples:
Wrong
Average Monthly Sales =
AVERAGEX (
VALUES ( 'Date'[Year Month] ),
SUM ( 'Sales'[Total Price] ) // Not filtered by year month!
)Explanation: AVERAGEX iterates over the year month column and creates a row context. There is NO filter context, so the result of SUM ( 'Sales'[Total Price] ) is not filtered by the current iteration of year month.
Correct
Average Monthly Sales =
AVERAGEX (
VALUES ( 'Date'[Year Month] ),
CALCULATE ( SUM ( 'Sales'[Total Price] ) ) // Is filtered by year month.
)Explanation: Introducing CALCULATE over the summation invokes a context transition, which means the row context can now filter the summation appropriately for the current iteration of year month.
Advanced Topics
As mentioned above, the book also covers some advanced DAX concepts. They talk about expanded tables and their implications on certain measures. The shadow filter context is also discussed, which is related to the ALLSELECTED() function. This is actually really important (among many important things). There is a golden rule of sorts circling the use of ALLSELECTED(), and that is: "If a measure contains ALLSELECTED() anywhere in the code, then that measure cannot be called by any other measure." Wow I know I'm guilty of doing just that!
Conclusion
The book is written well. The authors know that DAX is confusing and gently introduce concepts at an appropriate pace. I would advise against skipping sections unless you absolutely do not need to review it. For example, if your organization doesn't use calculation groups, you can probably skip it. Most chapters cover relavant topics for all business cases though.
What to read next?
If you're itching to learn more DAX, the same authors produced a follow-up book called Optimizing DAX. I've also written an article on that book as well.
Comments