- January 25, 2021
- Power BI
Performance problems 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 data model, DAX studio is needed to find the cause. However, this blog post is about the optimization of DAX code. Which, by the way, also requires DAX Studio!
What you need to know, simply put, is that the engine that processes your DAX is actually not one engine but two engines that work in close proximity: an extremely fast engine that can only process simple code, called the Storage Engine (SE). And a fast one, but certainly not as fast, that can process all code, called the Formula Engine (FE). This is how Microsoft managed to balance speed and complexity. What DAX performance tuning ultimately comes down to is shifting the workload from the FE to the SE. So that only requires using code that the Storage Engine can handle. There's a lot to say about the inner workings of these engines, but let me keep it as simple as possible by saying that the SE receives queries described in a language called xmSQL. Here is the xmSQL that the SE can handle.
List of available xm_SQL formulas
The code you have written now in your model s apparently slow, otherwise you wouldn t be reading this now 😉 . So how do you make sure you only use these formulas and therefore make it faster? Well, you probably fell into one of the "performance pitfalls" that you can get out of by following these optimization strategies.
1. Avoid using a FILTER table formula in a CALCULATE Boolean test (a true or false test). However, this first strategy has a few exceptions. You cannot avoid using a table filter with FILTER when:
- Multiple columns are required for the test
- You must name a measure for the test
- You need to use another CALCULATE for the test
2. If the use of FILTER cannot be avoided, use only the columns that are actually needed for the test instead of the entire table
3. Avoid IF statements
4. Avoid having to rely on the CallbackDataID
5. Avoid triggering a context transition when it is not needed
6. Avoid the use of double iterators whenever possible.
You want to base a calculation on the NYC cab dataset. More specifically, you want the trip distance divided by the trip amount, but only for trips where the trip amount does not equal 0, otherwise you would get an error. Here is your first attempt and the statistics of this code in DAX studio.
Now, I have the List of available xm_SQL formulas added for a reason 😉 . IF statements, for example, are not on it. And in this case, none are needed. You've already made sure that dividing by 0 doesn't result in 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 a little bit, but not as much as you probably hoped. So let's move on and see how the engine actually processes our code. Instead of looking at the statistics in DAX Studio, let's now look at the xmSQL in DAX Studio (at the bottom of this screenshot).
You don't have to read the entire xm_SQL in this case to see that a CallbackDataID is present. That means the SE can't do this all by itself and is asking for help from the FE, which is because the DIVIDE formula is not on the list of available xm_SQL formulas. And you do want to try to get the SE to do all the work. Now, what formulas can you find on the xmSQL formulas overview that could save the day? Exactly, FILTER and the / operator. So if you could manage to get rid of the trips where the fare_amount equals zero using FILTER, you could get rid of the DIVIDE formula by replacing it with the / operator and you would push the work to the SE. Let's give that a try.
Success! The DAX code no longer needs a CallbackDataID and we managed to reduce 62% of the initial processing time.
Last words
Writing faster DAX means achieving the same result in a faster and therefore different way. So you need to be able to write DAX at a decent level before you can start optimizing it. And even then, it won't be easy. But it is possible and you can learn it too! If only you... followed my DAX course through the Advanced Power BI course 😉 . You'll learn all about that and much more.
