How to Get Performance Data from Power BI with DAX Studio

Sometimes we have a slow Report, and we need to figure out why. I will show you how to collect performance data and what these metrics mean. The post How to Get Performance Data from Power BI with DAX Studio appeared first on Towards Data Science.

Apr 23, 2025 - 00:31

How to Get Performance Data from Power BI with DAX Studio

Introduction

To put things straight: I will not discuss how to optimize DAX Code today.

More articles will follow, concentrating on common mistakes and how to avoid them.

But, before we can understand the performance metrics, we need to understand the architecture of the Tabular model in Power Bi.
The same architecture applies to Tabular models in SQL Server Analysis Services.

Any Tabular model has two Engines:

Storage Engine
Formula Engines

These two have distinct properties and fulfill different tasks in a Tabular model.

Let’s investigate them.

Storage Engine

The Storage Engine is the interface between the DAX Query and the data stored in the Tabular model.

This Engine takes any given DAX query and sends queries to the Vertipaq Storage engine, which stores the data in the data model.

The Storage Engine uses a language called xmSQL to query the data model.

This language is based on the standard SQL language but has fewer capabilities and supports only simple arithmetic operators (+, -, /, *, =, <>, and IN).

To aggregate data, xmSQL supports SUM, MIN, MAX, COUNT, and DCOUNT (Distinct Count).

Then it supports GROUP BY, WHERE, and JOINs.

It will help if you have a basic understanding of SQL Queries when you try to understand xmSQL. If you don’t know SQL, it will be helpful to learn the basics when digging deeper into analyzing bad-performing DAX code.

The most important fact is that the Storage Engine is multi-threaded.

Therefore, when the Storage Engine executes a query, it will use multiple CPU-Cores to speed up query execution.

Lastly, the Storage Engine can Cache queries and the results.

Consequently, repeated execution of the same query will speed up the execution because the result can be retrieved from the cache.

Formula Engine

The Formula Engine is the DAX engine.

All functions, which the Storage Engine cannot execute, are executed by the Formula Engine.

Usually, the Storage Engine retrieves the data from the data model and passes the result to the Formula Engine.

This operation is called materialization, as the data is stored in memory to be processed by the Formula Engine.

As you can imagine, it is crucial to avoid large materializations.

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 and should be avoided, if possible.

Crucially, the Formula engine is single-threaded and has no Cache.

This means:

No parallelism by using multiple CPU Cores
No re-use of repeated execution of the same query

This means we want to offload as many operations as possible to the Storage engine.

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.

And this is another story that can fill entire books.

But how can we see how much time is used by each Engine?

Getting the Performance data

We need to have DAX Studio on our machine to get Performance Metrics.

We can find the download link for DAX Studio in the References Section below.

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.

But first, we need to get the DAX Query from Power BI.

First, we need to start Performance Analyzer in Power BI Desktop:

Figure 1 – Start Performance Analyzer in Power BI Desktop (Figure by the Author)

As soon as we see the Performance Analyzer Pane, we can start recording the performance data and the DAX query for all Visuals:

Figure 2 – Start recording of Performance data and DAX query (Figure by the Author)

First, we must click on Start Recording

Then click on “Refresh Visuals” to restart the rendering of all Visuals of the actual page.

We can click on one of the rows in the list and notice that the corresponding Visual is also activated.

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.

Figure 3 – Select the Visual and copy the query (Figure by the Author)

As we can see, Power BI needed 80’606 milliseconds to complete the rendering of the Matrix Visual.

The DAX query alone used 80’194 milliseconds.

This is a highly poor-performing measure used in this visual.

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:

Figure 4 – Start DAX Studio as an External Tool (Figure by the Author)

DAX Studio will automatically be connected to the Power BI Desktop file.

In case that we must start DAX Studio manually, we can manually connect to the Power BI file as well:

Figure 5 – Manually connect DAX Studio to Power BI Desktop (Figure by the Author)

After the connection is established, an empty query is opened in DAX Studio.

On the bottom part of the DAX Studio Window, you will see a Log section where you can see what happens.

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):

Figure 6 – Start Server Timings in DAX Studio (Figure by the Author)

After pasting the Query to the Empty Editor, we have to Enable the “Clear on Run” Button and execute the query.

Figure 7 – Enabling “Clear on Run” Feature (Figure by the Author)

“Clear on Run” ensures the Storage Engine Cache is cleared before executing the Query.

Clearing the Cache before measuring performance metrics is the best practice to ensure a consistent starting point for the measurement.

After executing the query, we will get a Server Timings page at the bottom of the DAX Studio Window:

Figure 8 – Server Timings Window in DAX Studio (Figure by the Author)

Now we see a lot of information, which we will explore next.

Interpreting the data

On the left side of Server Timings, we will see the execution timings:

Figure 9 – Execution Timings (Figure by the Author)

Here we see the following numbers:

Total – The total execution time in milliseconds (ms)
SE CPU – 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
FE – The time spent by the Formula Engine (FE) and the percentage of the total execution time
SE – The time spent by the Storage Engine (FE) and the percentage of the total execution time
SE Queries – The number of Storage Engine Queries needed for the DAX Query
SE Cache – The use of Storage Engine Cache, if any

As a rule of thumb: The larger the percentage of Storage Engine time, compared to Formula Engine time, the better.

The middle section shows a list of Storage Engine Queries:

Figure 10 – List of Storage Engine queries (Figure by the Author)

This list shows how many SE Queries have been executed for the DAX Query and includes some statistical columns:

Line – 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.
Subclass – Normally “Scan”
Duration – Time spent for each SE Query
CPU – CPU Time spent for each SE Query
Par. – Parallelism of each SE Query
Rows and KB – Size of the materialization by the SE Query
Waterfall – Timing sequence by the SE Queries
Query – The beginning of each SE Query

In this case, the first SE Query returned 12’527’422 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.

This clearly signifies that we made a big mistake with your DAX Code.

Lastly, we can read the actual xmSQL Code:

Figure 11 – Storage Engine Query Code (Figure by the Author)

Here we can see the xmSQL code and try to understand the Problem of the DAX Query.

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.

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.

As we can see from the timing, this takes a lot of time.

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.

Moreover, we know that the parallelism is only 1.9 times, which could be much better.

What it should look like

The DAX query contains only the Query created by Power BI Desktop.

But in most cases, we need the Code of the Measure.

DAX Studio offers a feature called “Define Measures” to get the DAX Code of the Measure:

Add one of two blank lines in the Query
Place the cursor on the first (empty) line
Find the Measure in the Data Model
Right-click on the Measure and click on Define Measure

Figure 12 – Define Measure in DAX Studio (Figure by the Author)

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

The result is a DEFINE statement followed by one or more MEASURE Statements containing the DAX code of our guilty Measure.

After optimizing the code, I executed the new Query and took the Server Timings to compare them to the original Data:

Figure 13 – Comparing slow a fast DAX code (Figure by the Author)

Now, the entire query took only 55 ms, and SE created a materialization of only 19 Rows.

The parallelism is at 2.6 times, which is better than 1.9 times. It looks like the SE didn’t need that much processing power to increase parallelism.

This is a very good sign.

The optimization worked very well after looking at these numbers.

Conclusion

We need some information when we have a slow Visual in your Power BI Report.

The first step is to use Performance Analyzer in Power BI Desktop to see where time is spent rendering the result of the Visual.

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.

I didn’t cover any methods to optimize DAX in this article, as it wasn’t my aim to do it.

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.

I’m looking forward to the journey with you.