Pages
531
Published
2020
Reusable DAX solutions for common Power BI and Analysis Services business intelligence scenarios
Build reliable Power BI reports faster by applying proven DAX patterns to the business problems you face every week.
DAX Patterns is a practical reference for Power BI developers and data analysts who need reliable, field-tested DAX solutions without reinventing the wheel on every project. Marco Russo and Alberto Ferrari distill years of real-world consulting into a catalog of reusable patterns covering time intelligence, statistical calculations, segmentation, ranking, and more. At 531 pages, it is dense with working code, worked examples, and clear explanations of why each pattern is built the way it is.
Most DAX problems have already been solved. The challenge is knowing the correct pattern, understanding why it works, and adapting it cleanly to your data model. That is precisely what DAX Patterns delivers.
Marco Russo and Alberto Ferrari are the authors behind SQLBI, the most-cited technical resource in the Power BI community. In this book they collect the patterns they have applied repeatedly across hundreds of client engagements — time intelligence, ABC classification, basket analysis, budgets, running totals, survey analysis, and a dozen more. Each pattern is presented with a working DAX implementation, a clear explanation of the logic, and guidance on common variations you are likely to encounter.
The book is organized so you can read it cover to cover to build systematic fluency, or open it to the specific pattern you need today. Either way, you come away with code you can drop into a real model and explanations clear enough that you can modify it confidently when your requirements differ from the example.
What separates this book from a collection of blog posts is coherence. The patterns share a consistent style, a consistent data model for examples, and a consistent level of rigor. You learn not just what to type but why the measure is structured that way — which filter context it relies on, which evaluation order matters, and where the naive approach silently returns wrong numbers.
If you write DAX professionally — in Power BI, Analysis Services, or Power Pivot — this book shortens the gap between a business question and a correct, maintainable measure. It does not teach DAX from scratch, but it does show you what mastery looks like in practice.
Covers year-to-date, quarter-to-date, prior period, and period-over-period growth measures. You learn the correct calendar table structure each pattern requires and how to adapt the measures to non-standard fiscal calendars.
Explains why standard aggregations fail for balances and headcounts, then walks through the LASTNONBLANK and OPENINGBALANCEYEAR patterns. You leave with measures that aggregate correctly across all date granularities.
Implements rolling window calculations and cumulative totals as reusable templates. You see how window size and blank-date handling affect results and how to make both patterns filter-context-safe.
Builds RANKX-based measures for product, customer, and category rankings, including dynamic top-N with an Others bucket. You learn to control ties and blank handling without breaking cross-filter behavior.
Walks through assigning ABC tiers and custom bands to entities based on cumulative contribution. You implement the pattern using variables to keep the logic readable and extend it to multiple segmentation dimensions.
Covers percentiles, weighted averages, standard deviation, and median for non-additive datasets. You adapt each formula to work correctly inside row context, filter context, and visual totals.
Shows how to model budget data stored at different granularities than actuals and write measures that join them without requiring a perfectly matching date spine. You handle missing periods and future dates explicitly.
Implements average-rate and end-of-period-rate currency conversion patterns that scale across multiple currencies and date grains. You learn where to store exchange rates and how measure structure affects performance.
Uses INTERSECT and CALCULATETABLE to find co-purchased products and shared customer segments across many-to-many relationships. You work through the data model choices that determine whether the pattern is correct and fast.
Addresses non-additive response data where simple SUM produces meaningless results. You build count-based and percentage-based distributions that remain accurate under any slicer combination.
Yes. The book does not teach DAX syntax from the ground up. You should be comfortable writing basic measures, understanding filter context, and using CALCULATE before picking this up. The authors' earlier book, The Definitive Guide to DAX, covers those foundations.
The patterns work across all tabular DAX engines: Power BI Desktop, Azure Analysis Services, SQL Server Analysis Services tabular mode, and Power Pivot for Excel. Minor function availability differences are noted where relevant.
The book references sample files and DAX code through SQLBI's official resources. Check the SQLBI website directly for any companion downloads associated with this edition.
The core DAX patterns covered are based on the DAX language itself, which is stable. Individual Power BI features change frequently, but the measure logic in this book remains valid. The publication date is August 2020, so verify that any patterns touching newer DAX functions are current against your version.
Both approaches work. The book is organized so each chapter stands alone as a reference you can consult when a specific scenario comes up, but reading sequentially builds a more systematic understanding of how the authors think about pattern construction.
It is not suitable for readers who are new to DAX or new to Power BI. It also does not cover Power BI report design, data modeling strategy, or M query — the focus is strictly on DAX measure patterns.
New
Build interactive dashboards and reports with Power BI Desktop, the Power BI service, and DAX
by Bradley Schacht, Devin Knight, Erin Ostrowsky, Mitchell Pearson
New
A practical guide to Power BI data modeling, DAX, and enterprise-grade report design
by Brett Powell, Greg Deckler, Leon Gordon
New
Advanced DAX techniques and data modeling patterns for Power BI professionals
by Philip Seamark, Thomas Martens