Performance issues in Power BI are usually caused by either the way the data in the model is structured or by inefficient DAX. When it comes to the datamodel, DAX studio is required in order to be able to find the cause. This blog post, however, is on the optimization of DAX code. Which, by the way, also requires DAX Studio!
What you need to be aware of, simply put, is that the engine that processes your DAX actually isn't one engine but two engines working close together: an extremely fast engine that can only process simple code called the Storage Engine (SE). And a fast, but certainly not as fast, one that can process all code, called the Formula Engine (FE). This is how Microsoft has managed to balance speed and complexity. What DAX performance tuning in the end pretty much comes down to is shifting the workload from the FE to the SE. So that requires only using code the Storage Engine can handle. There is a lot to say about the inner workings of these engines, but let me keep things as simple as possible by saying that the SE receives queries which are described in a language called xmSQL. Here is the xmSQL the SE can process:
List of available xm_SQL formulas
The code you've written now in your model s apparently slow or you wouldn't still be reading this 😉. So how do you make sure to only use these formulas and thereby make it faster? Well, you probably fell into one of the "performance traps" which you can get out of by following these optimization strategies:
- Avoid using a FILTER table formula in a CALCULATE Boolean test (a true or false test). However, this first strategy comes with a few exceptions. You cannot avoid a table filter using FILTER when:
- Multiple columns are required for the test
- You need to reference a measure for the test
- You need to use another CALCULATE for the test
- When using FILTER cannot be avoided, only use the columns that are actually needed for the test instead of the entire table
- Avoid IF statements
- Avoid having to rely on the CallbackDataID
- Avoid triggering context transition when it isn't required
- Avoid using double iterators whenever possible
You want to base a calculation on the NYC taxi data set. More specifically, you want the trip distance divided by the fare amount, but only for trips where the fare amount does not equal 0 or you would get an error. Here is your first try and the statistics of this code in DAX studio.
Now, I've added the List of available xm_SQL formulas for a reason 😉. For instance, IF statements aren't on there. And in this case, one isn't required. You've already ensured that dividing by 0 doesn't result into an error by using the DIVIDE formula instead of the / operator.
So let's rewrite this code and KISS (Keep It Simple Stupid!).
So that helped some but not as much as you were probably hoping for. So let's continue and see how the engine actually processed our code. Instead of looking at the stats in DAX Studio, now let's look at the xmSQL in DAX Studio (at the bottom of this screenshot).
You don't need to read all of the xm_SQL in this case to see that a CallbackDataID is present. That means the SE cannot do this all by itself and requests help from the FE which is due to the DIVIDE formula not being on the List of available xm_SQL formulas. And you do want to try to get the SE to do all of the work. Now, which formulas can you find on the xmSQL formula overview that could save the day? Exactly, FILTER and the / operator. So if you would manage to get rid of the trips where the fare_amount equals zero using FILTER, you can get rid of the DIVIDE formula by replacing it by the / operator and you'd be pushing the work to the SE. Let's give that a try:
Succes! The DAX code no longer requires a CallbackDataID and we've managed to cut 62% of the initial processing time.
Writing faster DAX means getting to the same result in a faster and therefore different way. So you're going to need to be able to write DAX at a pretty serious level before getting into optimizing it. And then still, it won't be easy. But it can be done and you can learn how to as well! Just as long as… you have taken my DAX course followed by the Advanced Power BI course 😉. You'll learn all about this and much more.