Modern DAX in Power BI: UDFs, Window Functions, INFO & More (Part 4)

Video by: Reid Havens

DAX has shipped more new surface area since 2022 than in the five years before it, and most of that never made it into anyone's habits. Part 4 of the series covers the five additions that change how you actually write code, where each one earns its keep, and where it still falls short.

User-defined functions are the headline. Reusable, parameterized DAX stored as a model object, so the pattern you keep copying between six measures finally lives in one place. We build one, call it with and without default parameters, and get into the limitations that are still there in the current release.

From there it's window functions (OFFSET, INDEX, WINDOW, RANK, ROWNUMBER) for row-relative math that time intelligence can't reach, INFO functions for querying your own model metadata as a table, EVALUATEANDLOG for debugging without littering the model with temporary measures, and dynamic format strings that follow context instead of sitting fixed on the measure.

The last stretch runs through the smaller additions that still come up: TREATAS, GROUPBY, DETAILROWS, ERROR, and TOCSV/TOJSON. None of them need a section of their own, but skipping them leaves gaps.

RELATED CONTENT 🔗

DAX Foundations Guide (Series Part 1)
DAX Context Guide (Series Part 2)
DAX Patterns Guide (Series Part 3)
DAX Modern Guide (Series Part 4)

DAX Fundamentals Part 3: The Patterns That Make Reports Work

You've learned how filter context and CALCULATE actually work. This is where it pays off. Part 3 of the DAX Fundamentals series covers the patterns you'll keep reaching for in real models, and why they behave the way they do.

We get into time intelligence (YTD, QTD, prior cycle, and why these functions return tables of dates rather than numbers), variables for cleaner and faster formulas, and SWITCH as the cure for nested IF chains nobody wants to maintain. Then inactive relationships and USERELATIONSHIP for when one fact table has two date columns fighting over the same date table.

The pattern that saves the most pain is calculation groups. Write three base measures, add one calc group with YTD, Prior Cycle, and YoY%, and you've replaced nine measures (and growing) with something you maintain in one place.

The video closes on the part most people skip: how the engine splits work between the Storage Engine and the Formula Engine, and why pushing work toward simple aggregations on a star schema is the difference between fast DAX and slow DAX.

(Livestream Replay) [DAX] Best Practices 101 for Optimization & Performance - with Alberto Ferrari

ABSTRACT 📄

Despite being usually very fast, DAX can be optimized, like any other computer system. Optimizing DAX requires a deeper understanding of the internals of the engine, because most optimizations are not intuitive. We will talk about the basics of optimizations, looking at some examples of code and discussing together ideas and options to make the code faster.

GUEST BIO 👤

Alberto started working with SQL Server in 2000 and immediately his interest focused on Business Intelligence. He and Marco Russo created sqlbi.com, where they publish extensive content about Business Intelligence.

Alberto published several books about Analysis Services, Power BI, and Power Pivot. He is a Microsoft MVP and he earned the SSAS Maestro title, the highest level of certification on Microsoft Analysis Services technology.

Today, Alberto's main activities are in the delivery of DAX and data modeling workshops for Power BI and Analysis Services all around the world. Alberto offers consulting services on large and complex data warehouses to provide assessments and validation of project analysis or to perform specific problem-solving activities.

Alberto is a well-known speaker at many international conferences, like PASS Summit, Sqlbits, and Microsoft Ignite. He loves to be on stage both at large events and at smaller user groups meetings, exchanging ideas with other SQL and BI fans. When traveling for work, he likes to engage with local user groups to provide evening sessions about his favorite topics. Thus, you can easily meet Alberto by looking up local Power BI user groups during scheduled courses.

Outside of SQLBI, most of Alberto's personal time is spent practicing video games, in the vain hope of eventually beating his son.

RELATED CONTENT 🔗

SQLBI Website

(Livestream Replay) What's new in DAX Studio 3? - with Darren Gosbell

Join Darren Gosbell and I for this live stream and hear about what is new and improved in DAX Studio 3! You’ll discover lots of amazing new capabilities, U/I improvements, feature enhancements, and even DARK MODE. Tune in to learn more!

GUEST BIO 👤

Darren Gosbell is a Senior Program Manager on the Power BI Customer Advisory Team with a passion for Data Visualization and Data Modelling. He has been working with the Microsoft Data Platform since 1999 and has practical experience implementing data warehousing and business intelligence solutions for some of Australia’s largest corporations. Prior to joining Microsoft Darren was awarded as a Microsoft Data Platform MVP from 2006 to 2022 in recognition of his contribution to the community especially in the areas of Analysis Services and Power BI, MDX and DAX. Darren is a contributor to a number of open source projects including DAX Studio, BI Developer Extensions (formerly called BIDS Helper) and ASStoredProcedures.

RELATED CONTENT link🔗

DAX Studio
Darren's Website
Darren’s Twitter