Name: Extreme DAX Take your Power BI and Fabric analytics skills to the next level (Michiel Rozema, Madzy Stikkelorum etc.)（Z-Library）
Author: Unknown Author

Page 1

EXPERT INSIGHT WHAT YOU WILL LEARN: • Understand semantic models and data modeling principles • Master DAX evaluation context and context transition • Build advanced DAX measures and calculations • Use table functions and fi ltering techniques like TREATAS • Implement calculation groups and fi eld parameters • Create custom calendars and time intelligence solutions • Apply regression and goal-seeking techniques • Secure data with att ribute-level controls This book helps experienced analysts unlock the full potential of DAX in Power BI and Microsoft Fabric to build scalable, production-ready analytics solutions. You'll begin by strengthening your understanding of semantic models, data modeling, and evaluation context—the foundation of reliable analytics. Instead of isolated examples, the book uses real-world scenarios such as inventory analysis, benchmarking, and data security to show how advanced DAX calculations are applied in practice. As you progress, you'll master advanced techniques including context transition, table functions, calculation groups, fi eld parameters, and custom calendars. You'll also explore analytical methods such as regression and goal-seeking to generate deeper insights from your data. With step-by-step guidance and practical PBIX examples, you'll learn how to design eff icient models, avoid common pitfalls, and implement reusable calculations. By the end, you'll be able to create advanced DAX measures and high-performance Power BI solutions for complex analytical requirements. Extreme DAX Second Edition www.packt.com Get a free PDF of this book packt.link/free-ebook/9781836647638 Extreme DAX Take your Power BI and Fabric analytics skills to the next level Second Edition Michiel Rozema Madzy Stikkelorum Henk Vlootman Foreword by Jeroen ter Heerdt Former DAX Product Manager, Microsoft Power BI M ichiel Rozem a M adzy Stikkelorum H enk V lootm an Extrem e D A X Second Edition

Page 2

Extreme DAX Second Edition Take your Power BI and Fabric analytics skills to the next level Michiel Rozema Madzy Stikkelorum Henk Vlootman

Page 3

Extreme DAX Second Edition Copyright © 2026 Packt Publishing All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author nor Packt Publishing, or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information. Portfolio Director: Sunith Shetty Relationship Lead: Apeksha Shetty Project Manager: Shashank Desai Content Engineer: Ayushi Bulani Technical Editor: Seemanjay Ameriya Copy Editor: Ayushi Bulani Indexer: Rekha Nair Proofreader: Ayushi Bulani Production Designer: Shantanu Zagade Growth Lead: Merlyn M Shelley First Published: January 2022 Second Edition: May 2026 Production reference: 1300426 Published by Packt Publishing Ltd. Grosvenor House 11 St Paul's Square Birmingham B3 1RB, UK ISBN 978-1-83664-763-8 https://www.packtpub.com

Page 4

In memory of my father, Anne Rozema, who passed away at the end of 2025. This is my first publication that he won't proudly collect on his bookshelf. – Michiel Rozema To the two most important Henks in my life: Henk Vlootman, who gave me the opportunity and the time to write the second edition of his book, and for believing that I could. And to my dad/pai, Henk Groenveld, from whom I have inherited the urge, the ability, and creativity to solve complex puzzles. Thank you! – Madzy Stikkelorum

Page 5

Foreword DAX has a way of humbling you. It for sure humbled me, many times over. You stare at a problem, you sense that a solution exists somewhere in the language, and yet the gap between that intuition and a working formula can feel impossibly wide. Since you are holding this book, you have been there. You already understand why Extreme DAX exists. To explain what makes it distinctive, let me take a brief detour into learning science. Researchers distinguish two modes of acquiring skill. The first is declarative learning: building knowledge through concepts, rules, and theory. The second is procedural learning: building knowledge through doing, through practice, through the gradual accumulation of pattern and intuition. Most skills require both, and most learners have a natural preference for one or the other. For certain skills, like riding a bike, no amount of reading substitutes for getting on and falling off. DAX has no shortage of excellent declarative learning materials. Books and courses that explain how the engine works, how contexts interact, and how functions are designed. These are genuinely valuable. They give you the scaffolding. What has been harder to find is the procedural counterpart: a resource that sits beside you while you work, shows you how experienced practitioners think through real problems, and hands you solutions you can put to use today. That is the gap Extreme DAX fills. The book is light on theory by design. Its weight is in curated, real-world scenarios—the kind you encounter in actual projects rather than contrived exercises. Reading it is close to the experience of looking over the shoulder of someone who has solved your exact problem before. This second edition has been updated and expanded to reflect the latest developments in the DAX language, many of which I had a direct role in shaping. It incorporates best practices refined through years of daily work with the language at its edges. My recommendation: spend time with the early chapters to sharpen your foundations, then treat the rest as a reference you return to whenever the work gets hard. It will get hard. Extreme DAX will be ready when it does. Jeroen ter Heerdt DAX Product Manager for Power BI at Microsoft (until March 2026)

Page 6

Contributors About the authors Michiel Rozema is one of the world's top Power BI and DAX experts. He holds a master's degree in mathematics and has worked in the IT industry for over 30 years as a consultant and manager. Michiel was the data insight lead at Microsoft Netherlands and launched Power BI in the country. He is the author of multiple books and video courses on Microsoft Fabric and Power BI. Michiel works as a data analyst and architect for his company Quanto, organizes the yearly Power BI Summer School, and regularly speaks at conferences on Power BI and Fabric. He has been awarded the Microsoft MVP award since 2019. Madzy Stikkelorum has a master's degree in mathematics, and analyzing data has always been one of her passions. She currently works at Quanto, where she enjoys helping clients solve complex problems with Microsoft Power BI solutions. She shares her love for Power BI and DAX by organizing the Power BI Summer School and as a speaker at international conferences. She is co-author of the book Microsoft Power BI Visual Calculations: Simplifying DAX and she launched a video training about DAX with Michiel. Madzy lives with her husband and two sons in the Netherlands, and loves to read, bake, and work on creative projects of all kinds in her spare time. Henk Vlootman has worked as an Excel consultant since 1992, when he started his company. With the release of Power Pivot, his focus shifted to business intelligence and data analytics. He now runs the consultancy firm Quanto together with Michiel, specializing in Power BI and Fabric. Henk is one of the founders of the Dutch Power BI user group and has published many books on Excel, Power Pivot, and Power BI. Henk has held the Microsoft MVP award since 2013. We would like to thank our families for their ongoing love and support. Many thanks to our technical reviewers, Jeffrey Wang and Terence Brown, for their valuable tips and feedback, and to the Packt team for all their support and patience with us all the times when we wanted to include the latest DAX innovations.

Page 7

About the reviewers Terence Brown is a distinguished data professional with over 14 years of industry experience, including an 8-year tenure at Microsoft where he developed extensive technical leadership and expertise. He is currently serving as an Engineering Manager at Trility Consulting. A highly skilled and certified practitioner of both Databricks and Microsoft Fabric, Terence leverages these advanced platforms to spearhead digital transformation and AI initiatives for his clients. His comprehensive background in data engineering and strategic consulting enables organizations to architect robust, scalable systems and realize the full strategic value of their data assets. Jeffrey Wang has spent over 20 years on Microsoft's BI product team and is a co-inventor of DAX, which he has guided from its earliest days to widespread global adoption. He now focuses on the frontier of AI-assisted and fully agentic development within the Power BI ecosystem.

Page 8

Table of Contents Preface xix Free benefits with your book ............................................................................. xxv Chapter 1: Analyzing Data with DAX 1 The five-layer model for business intelligence ....................................................... 2 Enterprise BI and end-user BI ............................................................................... 4 Fabric and Power BI .............................................................................................. 6 Where DAX fits in, and where to find it .................................................................. 7 Excel • 8 Power BI and Fabric • 8 SQL Server Analysis Services • 8 Azure Analysis Services • 8 Tools to develop semantic models and DAX ........................................................... 9 Powered by DAX: visual, interactive reports ......................................................... 10 The data-driven transformation cycle .................................................................. 12 How to approach solution development ............................................................... 14 Using semantic models for BI solution development • 15 We do not know exactly what we need • 16 Our data is not correct • 17 Summary ........................................................................................................... 18 Chapter 2: Model Design 21 Columnar data storage ........................................................................................ 21 Relational databases • 22 Columnar databases • 23 Data types and encoding ..................................................................................... 23 Relationships ..................................................................................................... 26

Page 9

Data in Excel • 26 Data in relational databases • 27 Power BI's relational model • 28 Relationship properties • 30 Active and inactive relationships • 30 Cross filter direction • 31 Cardinality • 34 Limited relationships • 35 Effective model design ........................................................................................ 35 Star schemas and snowflakes • 36 The issue with star schemas • 37 RDBMS principles to avoid in Power BI models • 38 Interdependent dimensions • 38 One fact table only • 40 Data warehouse as the single source of truth • 41 Using many-to-many relationships • 41 Memory and performance considerations ........................................................... 43 Architectural options in semantic models ........................................................... 45 Import models • 46 DirectQuery • 47 DirectLake • 48 Composite models • 48 Import and DirectQuery • 49 DirectQuery from different sources • 49 DirectLake and import • 49 DirectQuery to semantic models • 49 Semantic link • 50 Summary ........................................................................................................... 50 Chapter 3: Using DAX 51 Technical requirements ...................................................................................... 52 Calculated columns ............................................................................................ 52 Table of Contents viii

Page 10

Calculated tables ................................................................................................ 54 Measures ............................................................................................................ 56 Visual calculations ............................................................................................. 58 DAX security filters ............................................................................................. 60 Field parameters ................................................................................................ 60 Calculation groups .............................................................................................. 61 DAX queries ........................................................................................................ 62 User-defined functions ....................................................................................... 64 Date tables ......................................................................................................... 67 Creating a date table • 68 Best practices in DAX .......................................................................................... 70 Think in terms of DAX measures primarily • 70 Build explicit measures • 71 Use base measures as building blocks • 71 Hide model elements • 72 Do not mix data and measures – use measure tables instead • 72 Table types • 74 Summary ........................................................................................................... 75 Chapter 4: Context and Filtering 77 The Power BI model ............................................................................................ 78 Introduction to DAX context ............................................................................... 78 Row context • 79 Query context • 81 Filter context • 83 Detecting filters • 84 Comparing query and filter context to row context • 85 DAX filtering: using CALCULATE ......................................................................... 85 Step 1: Setting up a filter context • 87 Step 2: Removing existing filters • 89 Step 3: Applying new filters • 89 Step 4: Evaluating the expression to calculate • 90 ix Table of Contents

Page 11

Removing filters with ALL functions ................................................................... 92 Time intelligence ................................................................................................ 95 Changing relationship behavior .......................................................................... 99 Table functions in DAX ..................................................................................... 102 Table aggregations • 102 Using virtual tables • 104 Context in table functions • 106 Performance considerations using table functions • 109 Filtering with table functions ............................................................................. 111 Using CALCULATETABLE • 111 Filters and tables • 113 Using TREATAS • 117 DAX variables .................................................................................................... 119 DAX queries ....................................................................................................... 122 Summary .......................................................................................................... 123 Chapter 5: Security with DAX 125 Technical requirements ..................................................................................... 125 Introduction to row-level security (RLS) ............................................................ 126 Security roles • 126 DAX security filters • 128 Security filters and relationships • 129 Dynamic RLS • 131 Modeling considerations for RLS • 134 Testing security roles • 138 Securing hierarchies using PATH functions ........................................................ 141 Hierarchical tables • 141 Introducing PATH functions • 142 PATH • 142 PATHCONTAINS • 143 PATHLENGTH • 143 PATHITEM • 143 Table of Contents x

Page 12

PATHITEMREVERSE • 143 Using PATH functions in RLS • 144 Advanced hierarchy navigation in RLS • 144 Securing attributes ............................................................................................ 147 The case for secured attributes • 147 Object-level security and its restrictions • 148 Dynamically securing attributes: introducing value-level security • 149 VLS: modeling • 149 VLS: security filters • 152 VLS: advanced scenarios • 154 How to develop in models with value-level security • 157 Dealing with multi-role membership • 158 Securing aggregation levels ............................................................................... 162 Measures cannot be secured, but fact tables can • 162 Restricting fact table granularity • 163 Securing aggregation levels with composite models • 163 Combining aggregation security with VLS • 168 Securing an aggregation level as an attribute • 171 Summary .......................................................................................................... 175 Chapter 6: Dynamically Changing Visualizations 177 The business case .............................................................................................. 178 Dynamic measures ........................................................................................... 180 The basic KPI measures • 180 Creating a field parameter • 182 Creating a visual with dynamic measures • 185 Excluding certain measure combinations • 186 Dynamic labels ................................................................................................. 189 Creating a field parameter • 189 Creating a visual with dynamic measures and dynamic axes • 191 Dynamic report titles ......................................................................................... 194 Excluding combinations of specific measures and labels ..................................... 197 xi Table of Contents

Page 13

Dynamic date selection ..................................................................................... 203 Summary ......................................................................................................... 209 Chapter 7: Inventory Analysis 211 Data modeling for status-oriented data .............................................................. 212 Inventory granularity • 216 The business case and model ............................................................................. 217 Basic inventory calculations .............................................................................. 219 Inventory targets • 223 Inventory forecasting ....................................................................................... 232 Two types of forecasts • 232 Using a sales forecast to predict inventory changes • 232 Using extrapolation to predict inventory changes • 237 Calculating long-lasting inventory • 244 Working with forecast-based inventory targets • 248 Using linear regression for extrapolating inventory • 249 Summary ......................................................................................................... 258 Chapter 8: Alternative Calendars 261 Week-based and Gregorian calendars ................................................................ 262 What is a week-based calendar? • 262 Week numbers • 263 Periods • 264 Quarters • 265 Years • 265 Creating a week-based calendar table ............................................................... 266 Setting up dates • 266 Finding the correct start date • 267 Finding the correct end date • 267 Creating year and week columns • 268 Creating additional columns • 270 Defining a custom calendar ............................................................................... 271 Table of Contents xii

Page 14

Time intelligence calculations for week-based calendars .................................... 273 The Power BI model • 274 Calculating cumulative results • 275 Calculating sales growth • 278 Moving average by week • 285 Organize time intelligence with calculation groups ........................................... 288 Excluding measures from calculation groups • 290 Drill-through interaction with calculation groups • 294 Calculation formats on drill-through pages • 297 Keeping your report current ............................................................................. 299 The date selection table • 300 Creating selection options • 302 Applying date selection • 305 Summary ......................................................................................................... 306 Chapter 9: Working with Auto-Exist 307 Introducing the Power BI model ....................................................................... 308 How Power BI visualizes the output of a model .................................................. 309 Visual filters and context • 309 How using measures changes the behavior of visuals • 311 Understanding a visual's DAX query ................................................................... 314 What Auto-Exist is, and what it does .................................................................. 317 Using multiple filters in a visual • 317 How Auto-Exist optimizes DAX evaluation • 319 Example: The case of the missing workdays ....................................................... 323 The business case • 323 Model structure • 324 Sales analysis • 325 Extending the calendar table • 327 Workday analysis • 330 Where's my workday gone? • 332 How to solve the missing workdays problem • 334 xiii Table of Contents

Page 15

The root of the problem • 334 Changing model structure to get around Auto-Exist • 335 Always consider the context! • 337 Fixing the workday calculation • 340 Optimizing report performance with Auto-Exist ................................................ 341 Granularity in fact tables • 342 Filtering on multiple fact tables • 343 Optimizing model structure • 346 Optimizing the visual • 348 Using visual calculations to optimize performance • 349 Summary ......................................................................................................... 350 Chapter 10: Recursion in DAX 351 Concerning DAX user-defined functions ............................................................. 352 Function parameters • 352 Context in functions • 354 Parameter passing modes • 355 Naming conventions • 358 The business case ............................................................................................. 359 Intra-day time intelligence ............................................................................... 360 Analyzing shipments ........................................................................................ 365 Computing average shipment quantity • 366 Semi-recursion with DAX user-defined functions • 367 Compute results for a single hour • 369 Gather results for all hours • 371 Average shipment quantity • 373 Advanced recursion • 376 Consider the initial state • 376 Extending the recursion • 377 Leveraging calculated tables • 379 Summary .......................................................................................................... 381 Table of Contents xiv

Page 16

Chapter 11: DAX-Driven Waterfalls 383 Concerning visual calculations ......................................................................... 383 A simple example • 386 The business case and model ............................................................................ 388 Creating a waterfall with visual calculations ..................................................... 390 Setting up a simple all-positive waterfall • 390 Creating visual calculations for a simple waterfall • 393 Allowing negative values • 399 Coloring the deltas ........................................................................................... 407 Reusing visual calculations ................................................................................ 411 Creating a UDF for marking minimum and maximum values • 411 Creating waterfall UDFs • 413 Why do we need two hidden helper columns? • 418 A general function to call • 419 How to create a new waterfall chart • 420 Dynamic baseline and final values .................................................................... 422 Creating dynamic categories ............................................................................. 425 Summary ......................................................................................................... 428 Chapter 12: Benchmarking the Neighbors 429 Explaining the window functions ..................................................................... 429 Examples of the window functions • 431 Creating a dynamic revenue analysis • 435 Returning revenue of previous rows • 439 Returning a range of rows • 443 What else you can do with window functions • 445 Benchmarking ................................................................................................. 447 The business case and model • 447 Finding the neighbors • 448 How much sales do the neighbors have? • 456 Fixing the totals • 457 xv Table of Contents

Page 17

Finding different neighbors • 460 Making the number of neighbors dynamic • 463 Summary ......................................................................................................... 465 Chapter 13: Real-Estate Investment Planning 467 Financial calculations ....................................................................................... 468 Present value and net present value • 469 Internal rate of return • 470 Financial DAX functions .................................................................................... 471 The business case and model ............................................................................ 474 Creating adjustable rates and indexes ................................................................ 477 Calculating future values .................................................................................. 478 Initial investment and residual value • 478 Irregular cash flows • 480 Recurring cash flows • 481 Total future value • 485 Calculating net present value ............................................................................ 486 Calculating the internal rate of return ............................................................... 490 Calculating cost-covering rent .......................................................................... 493 Determining cost-covering rent by approximation • 494 Optimizing the approximation • 498 Solving for other variables ................................................................................ 503 Dynamically selecting a function • 504 A function for solving • 506 Solving for initial investment • 510 Summary .......................................................................................................... 511 Chapter 14: Unlock Your Exclusive Benefits 513 Unlock this Book's Free Benefits in 3 Easy Steps .................................................. 514 Table of Contents xvi

Page 18

Other Books You May Enjoy 518 Index 521 xvii Table of Contents

Page 19

(This page has no text content)

Page 20

Preface This book aims to help you bring your Power BI and Microsoft data analytics skills to the next level. Extreme DAX presents the Data Analysis Expressions (DAX) language but rather than going over all functions and concepts available, it covers a series of advanced business scenarios. These scenarios show you how DAX features and functions can be applied, solving advanced analytical challenges. Since its inception, now over ten years ago, Microsoft Power BI has become the leading platform for business intelligence. It really started some five years earlier with Power Pivot in Excel. The DAX language and the engine running semantic models and DAX have been pivotal in this: for the first time, Microsoft developed a database technology specifically aimed at analyzing large volumes of data. Next to Power BI and Power Pivot, DAX has been implemented in SQL Server and Microsoft Azure. And, with the advent of the Microsoft Fabric platform, DAX-based semantic models have become the centerpiece of everything data on the Microsoft platform. Meanwhile, the DAX language has evolved and is hardly recognizable from what it was fifteen years ago. This second edition, is proof of that: many of the scenarios from the first edition of Extreme DAX can be, and should be, solved in another way or can be optimized. We can even go beyond to things that were practically impossible three years ago! If you have read the first edition, you will recognize some of the scenarios in this book but you will also find many differences in the solutions. Throughout the book, you will find recently introduced concepts like field parameters, calculation groups, custom calendars, visual calculations, and user-defined functions. Microsoft Power BI and Fabric being the leading platforms for DAX now, naturally more differences will occur between what is possible there and what can be done in, say, Power Pivot in Excel or SQL Server Analysis Services. Your mileage may vary, as they say. Who this book is for If you are an analyst with a working knowledge of DAX in Power BI, Fabric, or other Microsoft analytics tools, this book will help you upgrade your DAX knowledge and work with analytical models more effectively. We expect readers to have some practical experience with DAX, although we try to introduce most concepts and DAX functions step by step.

Extreme DAX Take your Power BI and Fabric analytics skills to the next level (Michiel Rozema, Madzy Stikkelorum etc.)（Z-Library）

Extreme DAX Take your Power BI and Fabric analytics skills to the next level (Michiel Rozema, Madzy Stikkelorum etc.)（Z-Library）

Registered users can read the full content for free

AI Reading Assistant

Passage locations

Recommended for You

Tip the Site