The DAX Challenge
Over the years at SQLBI, we’ve created a wealth of resources—books, articles, videos, and courses—to help users unlock the full potential of DAX. However, we quickly realized that a substantial hurdle remained. Many users, no matter how competent, often find it challenging to adapt to the unique coding style that DAX requires.
It’s natural, of course. We tend to lean on what we already know. Users with backgrounds in Excel, SQL, or programming languages would often approach DAX the same way they approached these familiar tools. While this approach often led to code that worked, it was frequently far from ideal, particularly from a performance perspective.
An idea takes shape
A few years after the introduction of DAX in Power Pivot, it became clear that many users found it challenging to write efficient code due to the unique peculiarities of the DAX language. Even before Power BI hit the beta stage, it was already evident in Power Pivot and Analysis Services Tabular that more complex calculations presented new optimization issues.
DAX had no profiler, and the way it internally works differs from how you author the formula. Understanding these interactions required a technical background, but we believed it was possible to make this complexity accessible to business users not necessarily experienced in IT.
Initial thoughts and challenges
Our first idea was to create a service that could analyze a single formula and suggest potential improvements. But it became clear that this would have limited usefulness, as it wouldn’t be able to identify the actual bottlenecks in a report.
From the start, we knew collecting statistics on tables and columns in the model would be crucial. This information could help us to identify potentially costly parts of DAX formulas. However, achieving this would mean developing an agent that could inspect the model locally—a simple copy-paste of a DAX measure just wouldn’t cut it.
The statistics would also make expert DAX authors more productive. Instead of manually analyzing query plans and server timings information in DAX Studio, instead of trying to locate a bottleneck in DAX code by commenting measures in and out, instead of spending hours trying to find the reason why a report is slow, an automated system that locates a bottleneck and explains the possible issue would save many hours of work for each report. The need for such a tool was probably bigger than we initially thought.
The road to DAX Optimizer
Looking at the vast work required to bring this vision to life was intimidating, to say the least. One of the major challenges we encountered was the DAX parser. None of the existing “standard” parsers could provide more than just a tokenizer—a basic level of parsing that was not nearly enough for our ambitious project. This challenge was a familiar one, as we had already faced it with our previously launched service, DAX Formatter.
Despite these hurdles, the vision was clear. And so, with the name as the only certain piece of the puzzle, we registered the daxoptimizer.com domain.
More to come
So, here we are today, in the beta phase of DAX Optimizer, inviting people on our waitlist to join us on this exciting journey. There’s a lot of work still to do and many challenges to overcome, but we are fueled by the vision of making DAX more efficient for all users.
Thank you for being part of this journey with us. We can’t wait to share more stories from behind the scenes and updates about DAX Optimizer. Stay tuned!