Summary

In this chapter, we started with row and filter context and progressed to learning about optimization techniques for DAX functions that perform the same calculations. This is because the DAX pattern directly influences how Analysis Services retrieves data and calculates query results.

Using calculated columns can cause unneeded processing at the engine level instead of the import step. Using Power Query for additional columns helps with performance and optimization. DAX tuning tools are the go-to for examining the functions and their individual performance in the storage and formula engine. We recommended starting the Performance Analyzer to capture the queries to test in DAX Studio for server timings. It is important to look at the total duration, number of internal queries, and time spent in the engines.

Next, we looked at a range of common DAX pitfalls and alternative designs that can improve performance. We learned that, in general, we are trying to avoid formula...