Speed up your Power BI DAX formulas by up to 95% using the new OPTIMIZEDAX() function

4/2/2020 Edit: This is an April Fools’ joke. OPTIMIZEDAX() doesn’t exist, but we hope the post gives you a laugh nonetheless.

Have you ever seen the movie Limitless? In the film, Eddie Morra (played by Bradley Cooper) is a man whose life is in the dumps, but is turned around by a magic drug that enhances his mental acuity. With his new-found skills, he quickly rises to the top of Wall Street.

If there were an equivalent of Eddie in the Power BI world, it would be the performance of your complicated DAX measures – in the dumps.

Here’s usually how it goes – you first start to learn a bit of DAX and with your new-found superpowers, you start to build measures upon measures. But before you know it each click of your slicers takes 20 seconds… 30 seconds… 60 seconds?? Then you spend hours and hours tweaking a measure trying to get it faster.  Finally, after days and weeks of hair-pulling, you decide you have no option but to call in the professionals at P3 Adaptive to help.

That was the story up until the March 2020 release of Power BI Desktop, but finally, Microsoft has released a solution. Without much fanfare or publicity (more on that below), the Power BI team delivered a brand-spanking-new function: OPTIMIZEDAX().  This is the magic pill that will make the Vertipaq engine scream.

The function is simple – wrap it around any existing DAX measure and you’ll enjoy a 75-95% decrease in execution time.

To get you excited about its potential, let’s run some tests in DAX Studio. The query below is designed to stress the formula engine by forcing Vertipaq to materialize a 100m row table in memory and then return the row count of that table. This formula has been the gold standard for benchmarking CPU performance in Power BI and SSAS Tabular models. In our pre-OPTIMIZEDAX() example below, note that it takes about 10 seconds to execute.

BEFORE:

Now, let’s introduce OPTIMIZEDAX into the query.

AFTER:

10,179ms down to 305ms??? That is some serious improvement! The way it works is by forcing the Vertipaq engine to use multi-threading on Formula Engine calls, sort of like when a car engine is forced to generate horsepower beyond it’s designed range by injecting nitro (Nitrous Oxide). Keep in mind that DAX measures that utilize more Storage Engine than Formula Engine won’t benefit as much.

After seeing these performance numbers I know what you’re thinking. You’re wondering if there’s a way to automatically wrap every measure in your model with this new function. But before you do that, let’s come back to Limitless – just like the magic pill from the movie, there is a catch. You have to use OPTIMIZEDAX judiciously or risk some serious side effects. The primary one is that you can cause thermal throttling of the CPU clock speed, and in worst-case scenarios, there have been reports of server room fires and motherboard meltdowns. If using Power BI service or Azure Analysis Services, you could bring down an entire cloud cluster.

Our best-practice guidelines are to use it on no more than three measures per Power BI Report. If you are feeling risky and want to put it on four or more, we recommend ensuring that your server rack is water-cooled to mitigate the risk of a server room fire.

The second major limitation that we’ve seen is common to many of the powerful features that Microsoft has released for Power BI over the past few years: it’s only available on SSAS on-prem, Power BI Premium or Azure Analysis Services. We guess that Microsoft placed this limitation in place to isolate the overheating risk we described above, but if you’d like to see this on Power BI Pro, please upvote this idea and perhaps Microsoft will reconsider.

Given the incredible power inherent to this new function, we fully expect that most of our competitors are not going to be talking about OPTIMIZEDAX because they know it could put them out of business. At P3, we’ve always put empowering our clients above profits and so we want to be the first to advertise this new functionality loud and clear. To find out more about OPTIMIZEDAX() and to start implementing it in your reports, click here. Enjoy!