Introduction<\/mdspan><\/h2>\nTo put things straight: I will not discuss how to optimize DAX Code today.<\/p>\n
More articles will follow, concentrating on common mistakes and how to avoid them.<\/p>\n
But, before we can understand the performance metrics, we need to understand the architecture of the Tabular model in Power Bi<\/a>.
The same architecture applies to Tabular models in SQL Server Analysis Services.<\/p>\nAny Tabular model has two Engines:<\/p>\n
\n- Storage Engine<\/li>\n
- Formula Engines<\/li>\n<\/ul>\n
These two have distinct properties and fulfill different tasks in a Tabular model.<\/p>\n
Let\u2019s investigate them.<\/p>\n
Storage Engine<\/h2>\n
The Storage Engine is the interface between the DAX Query and the data stored in the Tabular model.<\/p>\n
This Engine takes any given DAX query and sends queries to the Vertipaq Storage engine, which stores the data in the data model.<\/p>\n
The Storage Engine uses a language called xmSQL to query the data model.<\/p>\n
This language is based on the standard SQL language but has fewer capabilities and supports only simple arithmetic operators (+, -, \/, *, =, <>, and IN).<\/p>\n
To aggregate data, xmSQL supports SUM<\/code>, MIN<\/code>, MAX<\/code>, COUNT<\/code>, and DCOUNT<\/code> (Distinct Count).<\/p>\nThen it supports GROUP BY<\/code>, WHERE<\/code>, and JOINs<\/code>.<\/p>\nIt will help if you have a basic understanding of SQL Queries when you try to understand xmSQL. If you don\u2019t know SQL, it will be helpful to learn the basics when digging deeper into analyzing bad-performing DAX code.<\/p>\n
The most important fact is that the Storage Engine is multi-threaded.<\/p>\n
Therefore, when the Storage Engine executes a query, it will use multiple CPU-Cores to speed up query execution.<\/p>\n
Lastly, the Storage Engine can Cache queries and the results.<\/p>\n
Consequently, repeated execution of the same query will speed up the execution because the result can be retrieved from the cache.<\/p>\n
Formula Engine<\/h2>\n
The Formula Engine is the DAX engine.<\/p>\n
All functions, which the Storage Engine cannot execute, are executed by the Formula Engine.<\/p>\n
Usually, the Storage Engine retrieves the data from the data model and passes the result to the Formula Engine.<\/p>\n
This operation is called materialization, as the data is stored in memory to be processed by the Formula Engine.<\/p>\n
As you can imagine, it is crucial to avoid large materializations.<\/p>\n
The Storage Engine can call the Formula Engine when an xmSQL-Query contains functions that the Storage Engine cannot execute.
This is operation id called CallbackDataID<\/code> and should be avoided, if possible.<\/p>\nCrucially, the Formula engine is single-threaded and has no Cache.<\/p>\n
This means:<\/p>\n
\n- No parallelism by using multiple CPU Cores<\/li>\n
- No re-use of repeated execution of the same query<\/li>\n<\/ul>\n
This means we want to offload as many operations as possible to the Storage engine.<\/p>\n
Unfortunately, it is impossible to directly define which part of our DAX-Code is executed by which Engine. We must avoid specific patterns to ensure that the correct engine completes the work in the least amount of time.<\/p>\n
And this is another story that can fill entire books.<\/p>\n
But how can we see how much time is used by each Engine?<\/p>\n
Getting the Performance data<\/h2>\n
We need to have DAX Studio on our machine to get Performance Metrics.<\/p>\n
We can find the download link for DAX Studio in the References Section below.<\/p>\n
If you cannot install the Software, you can get a portable DAX version from the same site. Download the ZIP file and unpack it in any local folder. Then you can start DAXStudio.exe, and you get all features without limitations.<\/p>\n
But first, we need to get the DAX Query from Power BI.<\/p>\n
First, we need to start Performance Analyzer in Power BI Desktop:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-10.jpg?ssl=1\")
Figure 1 \u2013 Start Performance Analyzer in Power BI Desktop (Figure by the Author)<\/figcaption><\/figure>\nAs soon as we see the Performance Analyzer Pane, we can start recording the performance data and the DAX query for all Visuals:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-73.png?ssl=1\")
Figure 2 \u2013 Start recording of Performance data and DAX query (Figure by the Author)<\/figcaption><\/figure>\nFirst, we must click on Start Recording<\/p>\n
Then click on \u201cRefresh Visuals\u201d to restart the rendering of all Visuals of the actual page.<\/p>\n
We can click on one of the rows in the list and notice that the corresponding Visual is also activated.<\/p>\n
When we expand on one of the rows in the report, we see a few rows and a link to copy the DAX query to the Clipboard.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-12.jpg?ssl=1\")
Figure 3 \u2013 Select the Visual and copy the query (Figure by the Author)<\/figcaption><\/figure>\nAs we can see, Power BI needed 80\u2019606 milliseconds to complete the rendering of the Matrix Visual.<\/p>\n
The DAX query alone used 80\u2019194 milliseconds.<\/p>\n
This is a highly poor-performing measure used in this visual.<\/p>\n
Now, we can start DAX Studio.
In case we have DAX Studio installed on our machine, we will find it in the External Tool Ribbon:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-68.png?ssl=1\")
Figure 4 \u2013 Start DAX Studio as an External Tool (Figure by the Author)<\/figcaption><\/figure>\nDAX Studio will automatically be connected to the Power BI Desktop file.<\/p>\n
In case that we must start DAX Studio manually, we can manually connect to the Power BI file as well:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-11.jpg?ssl=1\")
Figure 5 \u2013 Manually connect DAX Studio to Power BI Desktop (Figure by the Author)<\/figcaption><\/figure>\nAfter the connection is established, an empty query is opened in DAX Studio.<\/p>\n
On the bottom part of the DAX Studio Window, you will see a Log section where you can see what happens.<\/p>\n
But, before pasting the DAX Query from Power BI Desktop, we have to start Server Timings in DAX Studio (Right top corner of the DAX Studio Window):<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-67.png?ssl=1\")
Figure 6 \u2013 Start Server Timings in DAX Studio (Figure by the Author)<\/figcaption><\/figure>\nAfter pasting the Query to the Empty Editor, we have to Enable the \u201cClear on Run\u201d Button and execute the query.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-69.png?ssl=1\")
Figure 7 \u2013 Enabling \u201cClear on Run\u201d Feature (Figure by the Author)<\/figcaption><\/figure>\n\u201cClear on Run\u201d ensures the Storage Engine Cache is cleared before executing the Query.<\/p>\n
Clearing the Cache before measuring performance metrics is the best practice to ensure a consistent starting point for the measurement.<\/p>\n
After executing the query, we will get a Server Timings page at the bottom of the DAX Studio Window:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-71.png?ssl=1\")
Figure 8 \u2013 Server Timings Window in DAX Studio (Figure by the Author)<\/figcaption><\/figure>\nNow we see a lot of information, which we will explore next.<\/p>\n
Interpreting the data<\/h2>\n
On the left side of Server Timings, we will see the execution timings:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-70.png?ssl=1\")
Figure 9 \u2013 Execution Timings (Figure by the Author)<\/figcaption><\/figure>\nHere we see the following numbers:<\/p>\n
\n- Total \u2013 The total execution time in milliseconds (ms)<\/li>\n
- SE CPU \u2013 The sum of the CPU time spent by the Storage Engine (SE) to execute the Query.
Usually, this number is greater than the Total time because of the parallel execution using multiple CPU Cores<\/li>\n - FE \u2013 The time spent by the Formula Engine (FE) and the percentage of the total execution time<\/li>\n
- SE \u2013 The time spent by the Storage Engine (FE) and the percentage of the total execution time<\/li>\n
- SE Queries \u2013 The number of Storage Engine Queries needed for the DAX Query<\/li>\n
- SE Cache \u2013 The use of Storage Engine Cache, if any<\/li>\n<\/ul>\n
As a rule of thumb: The larger the percentage of Storage Engine time, compared to Formula Engine time, the better.<\/p>\n
The middle section shows a list of Storage Engine Queries:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-72.png?ssl=1\")
Figure 10 \u2013 List of Storage Engine queries (Figure by the Author)<\/figcaption><\/figure>\nThis list shows how many SE Queries have been executed for the DAX Query and includes some statistical columns:<\/p>\n
\n- Line \u2013 Index line. Usually, we will not see all the lines. But we can see all lines by clicking on the Cache and Internal buttons on the top right corner of the Server Timings Pane. But we will not find them very useful, as they are an internal representation of the visible queries. Sometimes it can be helpful to see the Cache queries and see what part of the query has been accelerated by the SE Cache.<\/li>\n
- Subclass \u2013 Normally \u201cScan\u201d<\/li>\n
- Duration \u2013 Time spent for each SE Query<\/li>\n
- CPU \u2013 CPU Time spent for each SE Query<\/li>\n
- Par. \u2013 Parallelism of each SE Query<\/li>\n
- Rows and KB \u2013 Size of the materialization by the SE Query<\/li>\n
- Waterfall \u2013 Timing sequence by the SE Queries<\/li>\n
- Query \u2013 The beginning of each SE Query<\/li>\n<\/ul>\n
In this case, the first SE Query returned 12\u2019527\u2019422 rows to the Formula engine (The number of rows in the entire Fact table) using 1 GB of Memory. This is not good, as large materializations like these are performance killers.<\/p>\n
This clearly signifies that we made a big mistake with your DAX Code.<\/p>\n
Lastly, we can read the actual xmSQL Code:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-75.png?ssl=1\")
Figure 11 \u2013 Storage \u00a0Engine Query Code (Figure by the Author)<\/figcaption><\/figure>\nHere we can see the xmSQL code and try to understand the Problem of the DAX Query.<\/p>\n
In this case, we see that there is a highlighted CallbackDataID. DAX Studio highlights all CallbackDataID in the Query text and makes all queries in the query list bold, which contains a CallbackDataID.<\/p>\n
We can see that, in this case, an IF() function is pushed to the Formula Engine (FE), as the SE cannot process this function. But SE knows that FE can do it. So, it calls the FE for each row in the result. In this case, over 12 million times.<\/p>\n
As we can see from the timing, this takes a lot of time.<\/p>\n
Now we know that we have written bad DAX Code and the SE calls the FE many times to execute a DAX function. And we know that we use 1 GB of RAM to execute the query.<\/p>\n
Moreover, we know that the parallelism is only 1.9 times, which could be much better.<\/p>\n
What it should look like<\/h2>\n
The DAX query contains only the Query created by Power BI Desktop.<\/p>\n
But in most cases, we need the Code of the Measure.<\/p>\n
DAX Studio offers a feature called \u201cDefine Measures\u201d to get the DAX Code of the Measure:<\/p>\n
\n- Add one of two blank lines in the Query<\/li>\n
- Place the cursor on the first (empty) line<\/li>\n
- Find the Measure in the Data Model<\/li>\n
- Right-click on the Measure and click on Define Measure<\/li>\n<\/ol>\n
![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-74.png?ssl=1\")
Figure 12 \u2013 Define Measure in DAX Studio (Figure by the Author)<\/figcaption><\/figure>\n5. If our Measure calls another Measure, we can click on Define Dependent Measures. In this case, DAX Studio extracts the code of all Measures used by the selected Measure<\/p>\n
The result is a DEFINE<\/code> statement followed by one or more MEASURE<\/code> Statements containing the DAX code of our guilty Measure.<\/p>\nAfter optimizing the code, I executed the new Query and took the Server Timings to compare them to the original Data:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/04\/image-13.jpg?ssl=1\")
Figure 13 \u2013 Comparing slow a fast DAX code (Figure by the Author)<\/figcaption><\/figure>\nNow, the entire query took only 55 ms, and SE created a materialization of only 19 Rows.<\/p>\n
The parallelism is at 2.6 times, which is better than 1.9 times. It looks like the SE didn\u2019t need that much processing power to increase parallelism.<\/p>\n
This is a very good sign.<\/p>\n
The optimization worked very well after looking at these numbers.<\/p>\n
Conclusion<\/h2>\n
We need some information when we have a slow Visual in your Power BI Report.<\/p>\n
The first step is to use Performance Analyzer in Power BI Desktop to see where time is spent rendering the result of the Visual.<\/p>\n
When we see that it takes much time to execute the DAX Query, we need DAX Studio to find out the problem and try to fix it.<\/p>\n
I didn\u2019t cover any methods to optimize DAX in this article, as it wasn\u2019t my aim to do it.<\/p>\n
But now that I have laid down the foundation to get and understand the performance metrics available in DAX Studio, I can write further articles to show how to optimize DAX code, what you should avoid, and why.<\/p>\n
I\u2019m looking forward to the journey with you.<\/p>\n
References<\/h1>\n
Download DAX Studio for free here: https:\/\/www.sqlbi.com\/tools\/dax-studio\/<\/a><\/p>\nFree SQLBI Tools Training: DAX Tools Video Course \u2013 SQLBI<\/a><\/p>\nSQLBI offers DAX-Optimization training as well.<\/p>\n
I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here<\/a>.<\/p>\nThe Contoso Data can be freely used under the MIT License, as described here<\/a>.<\/p>\n\n
The post How to Get Performance Data from Power BI with DAX Studio<\/a> appeared first on Towards Data Science<\/a>.<\/p>\n<\/div>\n
To put things straight: I will not discuss how to optimize DAX Code today.<\/p>\n
More articles will follow, concentrating on common mistakes and how to avoid them.<\/p>\n
But, before we can understand the performance metrics, we need to understand the architecture of the Tabular model in Power Bi<\/a>. Any Tabular model has two Engines:<\/p>\n These two have distinct properties and fulfill different tasks in a Tabular model.<\/p>\n Let\u2019s investigate them.<\/p>\n The Storage Engine is the interface between the DAX Query and the data stored in the Tabular model.<\/p>\n This Engine takes any given DAX query and sends queries to the Vertipaq Storage engine, which stores the data in the data model.<\/p>\n The Storage Engine uses a language called xmSQL to query the data model.<\/p>\n This language is based on the standard SQL language but has fewer capabilities and supports only simple arithmetic operators (+, -, \/, *, =, <>, and IN).<\/p>\n To aggregate data, xmSQL supports Then it supports It will help if you have a basic understanding of SQL Queries when you try to understand xmSQL. If you don\u2019t know SQL, it will be helpful to learn the basics when digging deeper into analyzing bad-performing DAX code.<\/p>\n The most important fact is that the Storage Engine is multi-threaded.<\/p>\n Therefore, when the Storage Engine executes a query, it will use multiple CPU-Cores to speed up query execution.<\/p>\n Lastly, the Storage Engine can Cache queries and the results.<\/p>\n Consequently, repeated execution of the same query will speed up the execution because the result can be retrieved from the cache.<\/p>\n The Formula Engine is the DAX engine.<\/p>\n All functions, which the Storage Engine cannot execute, are executed by the Formula Engine.<\/p>\n Usually, the Storage Engine retrieves the data from the data model and passes the result to the Formula Engine.<\/p>\n This operation is called materialization, as the data is stored in memory to be processed by the Formula Engine.<\/p>\n As you can imagine, it is crucial to avoid large materializations.<\/p>\n The Storage Engine can call the Formula Engine when an xmSQL-Query contains functions that the Storage Engine cannot execute. Crucially, the Formula engine is single-threaded and has no Cache.<\/p>\n This means:<\/p>\n This means we want to offload as many operations as possible to the Storage engine.<\/p>\n Unfortunately, it is impossible to directly define which part of our DAX-Code is executed by which Engine. We must avoid specific patterns to ensure that the correct engine completes the work in the least amount of time.<\/p>\n And this is another story that can fill entire books.<\/p>\n But how can we see how much time is used by each Engine?<\/p>\n We need to have DAX Studio on our machine to get Performance Metrics.<\/p>\n We can find the download link for DAX Studio in the References Section below.<\/p>\n If you cannot install the Software, you can get a portable DAX version from the same site. Download the ZIP file and unpack it in any local folder. Then you can start DAXStudio.exe, and you get all features without limitations.<\/p>\n But first, we need to get the DAX Query from Power BI.<\/p>\n First, we need to start Performance Analyzer in Power BI Desktop:<\/p>\n As soon as we see the Performance Analyzer Pane, we can start recording the performance data and the DAX query for all Visuals:<\/p>\n First, we must click on Start Recording<\/p>\n Then click on \u201cRefresh Visuals\u201d to restart the rendering of all Visuals of the actual page.<\/p>\n We can click on one of the rows in the list and notice that the corresponding Visual is also activated.<\/p>\n When we expand on one of the rows in the report, we see a few rows and a link to copy the DAX query to the Clipboard.<\/p>\n As we can see, Power BI needed 80\u2019606 milliseconds to complete the rendering of the Matrix Visual.<\/p>\n The DAX query alone used 80\u2019194 milliseconds.<\/p>\n This is a highly poor-performing measure used in this visual.<\/p>\n Now, we can start DAX Studio. DAX Studio will automatically be connected to the Power BI Desktop file.<\/p>\n In case that we must start DAX Studio manually, we can manually connect to the Power BI file as well:<\/p>\n After the connection is established, an empty query is opened in DAX Studio.<\/p>\n On the bottom part of the DAX Studio Window, you will see a Log section where you can see what happens.<\/p>\n But, before pasting the DAX Query from Power BI Desktop, we have to start Server Timings in DAX Studio (Right top corner of the DAX Studio Window):<\/p>\n After pasting the Query to the Empty Editor, we have to Enable the \u201cClear on Run\u201d Button and execute the query.<\/p>\n \u201cClear on Run\u201d ensures the Storage Engine Cache is cleared before executing the Query.<\/p>\n Clearing the Cache before measuring performance metrics is the best practice to ensure a consistent starting point for the measurement.<\/p>\n After executing the query, we will get a Server Timings page at the bottom of the DAX Studio Window:<\/p>\n Now we see a lot of information, which we will explore next.<\/p>\n On the left side of Server Timings, we will see the execution timings:<\/p>\n Here we see the following numbers:<\/p>\n As a rule of thumb: The larger the percentage of Storage Engine time, compared to Formula Engine time, the better.<\/p>\n The middle section shows a list of Storage Engine Queries:<\/p>\n This list shows how many SE Queries have been executed for the DAX Query and includes some statistical columns:<\/p>\n In this case, the first SE Query returned 12\u2019527\u2019422 rows to the Formula engine (The number of rows in the entire Fact table) using 1 GB of Memory. This is not good, as large materializations like these are performance killers.<\/p>\n This clearly signifies that we made a big mistake with your DAX Code.<\/p>\n Lastly, we can read the actual xmSQL Code:<\/p>\n Here we can see the xmSQL code and try to understand the Problem of the DAX Query.<\/p>\n In this case, we see that there is a highlighted CallbackDataID. DAX Studio highlights all CallbackDataID in the Query text and makes all queries in the query list bold, which contains a CallbackDataID.<\/p>\n We can see that, in this case, an IF() function is pushed to the Formula Engine (FE), as the SE cannot process this function. But SE knows that FE can do it. So, it calls the FE for each row in the result. In this case, over 12 million times.<\/p>\n As we can see from the timing, this takes a lot of time.<\/p>\n Now we know that we have written bad DAX Code and the SE calls the FE many times to execute a DAX function. And we know that we use 1 GB of RAM to execute the query.<\/p>\n Moreover, we know that the parallelism is only 1.9 times, which could be much better.<\/p>\n The DAX query contains only the Query created by Power BI Desktop.<\/p>\n But in most cases, we need the Code of the Measure.<\/p>\n DAX Studio offers a feature called \u201cDefine Measures\u201d to get the DAX Code of the Measure:<\/p>\n 5. If our Measure calls another Measure, we can click on Define Dependent Measures. In this case, DAX Studio extracts the code of all Measures used by the selected Measure<\/p>\n The result is a After optimizing the code, I executed the new Query and took the Server Timings to compare them to the original Data:<\/p>\n Now, the entire query took only 55 ms, and SE created a materialization of only 19 Rows.<\/p>\n The parallelism is at 2.6 times, which is better than 1.9 times. It looks like the SE didn\u2019t need that much processing power to increase parallelism.<\/p>\n This is a very good sign.<\/p>\n The optimization worked very well after looking at these numbers.<\/p>\n We need some information when we have a slow Visual in your Power BI Report.<\/p>\n The first step is to use Performance Analyzer in Power BI Desktop to see where time is spent rendering the result of the Visual.<\/p>\n When we see that it takes much time to execute the DAX Query, we need DAX Studio to find out the problem and try to fix it.<\/p>\n I didn\u2019t cover any methods to optimize DAX in this article, as it wasn\u2019t my aim to do it.<\/p>\n But now that I have laid down the foundation to get and understand the performance metrics available in DAX Studio, I can write further articles to show how to optimize DAX code, what you should avoid, and why.<\/p>\n I\u2019m looking forward to the journey with you.<\/p>\n Download DAX Studio for free here: https:\/\/www.sqlbi.com\/tools\/dax-studio\/<\/a><\/p>\n Free SQLBI Tools Training: DAX Tools Video Course \u2013 SQLBI<\/a><\/p>\n SQLBI offers DAX-Optimization training as well.<\/p>\n I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here<\/a>.<\/p>\n The Contoso Data can be freely used under the MIT License, as described here<\/a>.<\/p>\n \n The post How to Get Performance Data from Power BI with DAX Studio<\/a> appeared first on Towards Data Science<\/a>.<\/p>\n<\/div>\n
The same architecture applies to Tabular models in SQL Server Analysis Services.<\/p>\n\n
Storage Engine<\/h2>\n
SUM<\/code>, MIN<\/code>, MAX<\/code>, COUNT<\/code>, and DCOUNT<\/code> (Distinct Count).<\/p>\nGROUP BY<\/code>, WHERE<\/code>, and JOINs<\/code>.<\/p>\nFormula Engine<\/h2>\n
This is operation id called CallbackDataID<\/code> and should be avoided, if possible.<\/p>\n\n
Getting the Performance data<\/h2>\n
In case we have DAX Studio installed on our machine, we will find it in the External Tool Ribbon:<\/p>\nInterpreting the data<\/h2>\n
\n
Usually, this number is greater than the Total time because of the parallel execution using multiple CPU Cores<\/li>\n\n
What it should look like<\/h2>\n
\n
DEFINE<\/code> statement followed by one or more MEASURE<\/code> Statements containing the DAX code of our guilty Measure.<\/p>\nConclusion<\/h2>\n
References<\/h1>\n