Performance impact of visual calculations using DirectQuery in Power BI
Context
Some nice features came with the new version of Power BI Dekstop from February 2024. Especially two features got my attention, Visual Calculations and TMDL in Power BI Desktop. Today I want to talk about the visual calculations. I saw some Linkedln post talking about the performance improvement when using visual calculations and I was curious to check the performance impact with DirectQuery mode. Why DirectQuery mode? Well, most of the performance problems that I have seen so far happens with DirectQuery mode.
The goal of this post is to talk about the performance impact of using visual calculations in DirectQuery mode.
The data model
As an example, we are going to use a simplified version of the Contoso database (fact table with around 2 million rows). See below the data model. As you can see the tables are in DirectQuery Mode.
Performance impact
Two simple measures are used to compare the performance, sum of sales and cumulative sales. See below the DAX measure and the visual calculation used.
Let’s use Performance Analyzer to copy the DAX query used in the visual and then open DAX Studio to check the performance in details.
See below how both DAX queries looks like in DAX Studio:
As you can see, there is much more going on when using the visual calculation. In DAX Studio let’s turn “Query Plan” and “Server Timings” on and run both queries to compare their performance.
See below the “Server Timings” comparison:
Using the visual calculation the engine generates only one single SQL query, whereas using normal DAX measure multiple SQL queries are generated to return the result. Also, in the “Physical Query Plan” it can be seen that the query using normal DAX measure scans much more data than the visual calculation.
The result of this is a performance improvement from 5,9 seconds to 3,2 seconds!
You might be thinking right now, should I then start using visual calculations everywhere?
In my opinion, I wouldn’t do that! Visual calculations are still in preview and remember that visual calculations only apply for the visual itself. Caution should be taken to avoid creating visual calculations silos everywhere in your report. Of course, it could be a good and easy option to improve the performance. I think visual calculations can also help business users to start with DAX, but it doesn’t replace the need to learn DAX!
Conclusion
In this post we talked about the performance impact of using visual calculations in DirectQuery mode. Visual calculations can significantly improve the performance of your report. However, I wouldn’t start creating visual calculations everywhere in the report, in order to avoid visual calculations silos. Also, remember that this feature just came out and it is still in preview.
Last updated on February 22, 2024