Why Take the Time?
You may be thinking, "Oh, DAX is easy. I mean, it looks just like the functions in Excel!". That's what I thought too. On the surface, DAX does indeed look similar to Excel. But there's so much more to it - DAX carries the power of relational database functions. This is the main distinguishing factor from Excel, and is also the main reason for many headaches for unaware users.
I recently read Supercharge Power BI by Matt Allington. In the book, Matt covers the basics of DAX in an easily digestible writing style. He also provides practice exercises for us readers. This makes it an enjoyable and partially interactive reading experience. In this blog I'll go over some of the parts I found interesting.
Use Measures!
You should always use measures instead of calculated columns where possible. This becomes increasingly important as your data grows. Why? Matt helps us understand that, if you use a calculated column, it actually stores each value for every row in your model, which increases the size of your model and hampers the efficiency of the data compression process. Using a measure, on the other hand, only stores the calculation and doesn't come into play unless the measure is displayed in a visual.
Use a Matrix with Your Measures!
To aid in your conquest of DAX, Matt suggests using a matrix to help visually see your measures in action. You can start out by placing any categorical data in the Rows section and your measures in the Values section. You can do this one by one for each measure you have - this gives you quick visual feedback to see what your measures are doing.
Iterators and Row Context
Matt creates a great analogy for understanding iterator functions like SUMX() and AVERAGEX(). Visually, these are similar to their SUM() and AVERAGE() counterparts, but conceptually they perform and function differently. He says we can think of them as "checklist" functions, where they check off each row in the table that they iterate over. This gives them a row context. Where SUM() just performs an aggregate calculation for a single column, SUMX() will perform an expression for each row first (i.e. Sales[Amount] * Sales[TaxRate]), then finally sum up those results at the end.
Calculate() Shorthand Syntax
I was pleased to find that there is a shorthand method of writing CALCULATE() functions. I originally learned it the long way - you know, the one where you pass in the FILTER() function, then the ALL() function to finally get to the parameter you want to filter on. Matt clarifies that you can simply pass in the parameter without the FILTER() or ALL(). EX:
= CALCULATE( [Total Spent], Locations[StoreName] = "Tesla" )
As opposed to this:
= CALCULATE(
SUM( Purchases[Spent] ),
FILTER( ALL( Locations ), Locations[StoreName] = "Tesla" )
)
It's also great to know you can pass in other measures into the expression spot ([Total Spent] in the above example). This is possible because the calculate() function reevaluates the filters for anything passed into it.
Conclusion
Matt goes into much more detail on these topics and dives further into more complex material. If you are just starting out with DAX, or even if you already have experience with it and need a refresher on the basics, I highly recommend this book. Having a solid grasp on DAX foundations is absolutely essential if you are serious about being a professional Power BI user, and this book will allow you to jump to that next level. Oh, and as a bonus, Matt provides a free sample database to practice all of your DAX functions with.
