DAX Studio is a popular tool used by many Microsoft data professionals. It is commonly used as an advanced enterprise tool when working with tabular models. Are you new to DAX Studio or want to learn more about how to use it in combination with Power BI?
Follow along while I show you my go to scenarios of using DAX studio and Power BI together. This is part one of two.
What is DAX studio and why should i get it?
Before we get into the details. Lets start with an introduction to DAX Studio, as well as other third party tools.
Dax Studio is a free, third party software initially developed(and still maintained) by Darren Gosbell https://darren.gosbell.com/. It is commonly described as the ultimate tool for writing, authoring and analyzing DAX queries.
Yes, it is a third party tool and not maintained by or developed by Microsoft. However, there are currently no available official Microsoft tools that can offer the same amount of features as DAX Studio.
The same goes for many other third party community developed tools used for Power BI and tabular development. (You can find a good list of essential tools on SQLBI here).
In order to use DAX Studio and level up your workflow, you need to install it on your PC. Depending on your organisation, there might be some questions from your IT department because it’s a third party software. This is in many ways understandable and not very uncommon.
Marco Russo wrote a long article where he explains in detail about (among other things) the current gap between Microsoft developed and community developed enterprise development tools, which includes DAX Studio. This was followed up by Microsofts Amir Netz (CTO of Power BI) giving a rough background and explanation including a roadmap for more advanced development tools for tabular models in the future.
In that very same article he also states that third party community software is essential for many developers and in some cases it is also required.
External tools referenced on Microsoft Docs
Power BI has a feature called External tools. This allows you to connect your Power BI model directly to third party developed tools such as DAX Studio. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-external-tools.
DAX Studio is also listed under Open source tools on Microsoft docs for Analysis Services tools as well on other various MS docs site.
Microsoft Learn and exam DP-500
DAX Studio is listed under learning objectives in Microsoft learn for Optimizing Power BI performance.
DAX Studio (as well as other third party tools) is listed several times under “Skills measured” in the study guide for Exam DP-500: Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI
As you can see, the message is clear. Working professionally in Power BI requires you to both use and have good knowledge about third party tools, including DAX Studio. Having this knowledge is also measured in order to get Microsoft certifications. If you are a tabular developer it is also important to understand that in order to access the full set of features in tabular models, you need to use third party tools.
DAX Studio resources
You can download and install DAX Studio from DAX Studio’s website
The site also includes a documentation page that includes installation guides, list of features, tutorials and troubleshooting resources. You can also find all previous releases on the Downloads page. Or you can check out the git release page here that lists all versions and features.
Connect DAX Studio to Power BI
There are two main ways to connect to Power BI from DAX Studio.
Connect from DAX Studio
When you open DAX Studio you will be prompted to connect to a data source. This data source can be a PowerPivot Model, a Power BI desktop model or a Tabular server (Including a Power BI dataset in the service).
Connect from Power BI External tools
You can open DAX Studio from Power BI’s external tools ribbon. You need to have DAX Studio installed for it to appear on the external tools ribbon.
DAX studio will automatically connect to your local Power BI dataset under the hood when you open DAX Studio from Power BI External Tools ribbon.
Sometimes you may run into slow running visuals in Power BI. Power BI has a great tool called Performance Analyzer which gives you more detailed information on the execution times of the visual. However, this feature alone is often not enough to identify the reason behind a slow visual. Specially if the problem lies in the DAX code of the measure.
Let me go through an example by looking at a matrix visual that includes 6 measures. The matrix visual shows cost, profit, sales, sales quantity, number of orders and the average sales per order. The values are grouped by customer and a filter is set on Country to show only customers from France.
This visual has been identified as running slow in Power BI. Analyzing this kind of performance issues directly in Power BI Desktop does not offer the best experience. Usually you would need to break down the visual into separate visuals to investigate each measure by itself. There’s also no easy way to test changes to a DAX measure in Power BI desktop.
This can potentially result in a bloated .pbix file containing multiple test measures, test visuals, and test pages. This can cause massive headache over time as report complexity and demands grow.
This is where DAX Studio comes into play! Let me show you how to troubleshoot this visual and identify the problematic measure by using only DAX Studio.
Opening the query in DAX Studio
Before going to DAX Studio. Let’s open the Performance analyzer in Power BI Desktop. It has a function called “Copy query”. This allows you to copy the actual DAX query that was generated by the visual in Power BI. This query can then be used in DAX Studio and executed against the same Power BI model / dataset.
Pressing the “Copy query” button copies your DAX query into your clipboard. You can then paste the DAX query in the query area of DAX Studio
After pasting your query into DAX Studio it should look something similar to this.
The above query was generated by Power BI in order to show results in the matrix visual. However, there’s a bunch of things in this query that are unnecessary when wanting to troubleshoot the performance of the measures. It is highly unlikely that the auto generated DAX code is causing any issues. Therefore we want to focus on the DAX measures created by us. Don’t be intimidated by the code. If you do not have experience with the DAX language outside of writing DAX code in Power BI, there could be a number of new DAX statements showing here.
After removing the unnecessary DAX code we end up with the following code. As you can see, this is much more similar to the type of DAX used in DAX measures. However, there are two statements that you need to use in order to run the query in DAX Studio. Those are DEFINE and EVALUATE.
Running the query
We are only interested in the server side execution times because we are troubleshooting the execution time of the DAX query. To get this metrics we need to enable Server Timings trace. This can be done by navigating to Home -> Server Timings button.
Wait until the trace has started and run the query. You can run it by clicking the run button under Home -> Run. Changing to “Clear cache then run” is recommended as it will always clear the cache before every query execution. This allows you to see the true cold cache execution times of your query.
As you can see the result is quite similar as the result of the matrix visual in Power BI. The results are returned as a table. This is important to understand. Queries in DAX Studio needs to evaluate a table expression. This is different compared to measures in Power BI which are required to return a scalar value (a single value).
Troubleshooting the query
This particular query took 1462 milliseconds to complete. In many cases this number would be an acceptable response time for a visual in Power BI. However, in this particular case I know that the query should run faster. We can also compare this number to the number from Power BI’s Performance analyzer, which was 1145 ms. It is important to note that there is no simple way to clear the cache in Power BI Desktop. Is is therefore very difficult to draw any conclusions based on the numbers from Performance analyzer.
Let’s continue the troubleshooting. Are there any particular measure in this query that is causing the longer execution time? One good way to find out is to comment out the different measures and only run on measure at a time.
In this case the Sales measure was causing the longest execution times out of all measures. It took 503 ms to run which was the longest out of all measures in the query. One thing to know is that some of the other measures is based on the Sales measure. This means that the execution times of those measures also depends on the execution of measure Sales. Back to the Sales measure. Let’s find out how the DAX logic looks like!
Let’s add the DAX logic of measure Sales to the DAX query, so we can look at the code and edit it on the fly. This can be done by navigating to the measure in the metadata section of DAX Studio, and right click -> choose “Define Measure”.
DAX Studio then takes the DAX logic and defines it as Measure Sales in the current query. You can see in the example that it has added a section called MODEL MEASURES.
This allows you to run the query using either the current definition of measure Sales or using a modified definition, simply by editing the code. All is done without adding or changing any of the actual measures or objects stored in the Power BI dataset. Everything is handled inside of your DAX Studio session. However, it is still executing the queries against your Power BI dataset.
In this particular case I already know that this measure is causing some extra overhead by doing a FILTER() over the table Sales and only returning the rows where Net Price is more than 0.
There are two issues with this DAX code. The first is that you should never use FILTER() over a full table. The second is that this data does not contain rows with Net price below or equal to 0. Therefore, the filter is not needed in my case. Let’s remove the FILTER() section of my DAX measure and try to run the query again. Things can be much more complex in a real world scenario, of course. But this is only a demonstration of the functions and workflow of troubleshooting.
After editing the definition for measure Sales and uncommenting all the original measures in my DAX query I can see that the total execution time was reduced to 776 ms.
This is almost a 50% improvement of the execution time!
When executing the query with only measure Sales, we can see that the execution time is 106 ms. This is an improvement of around 80% for that single measure!
The end result is good enough for this demonstration. We can now go back to Power BI Desktop and edit the original measure Sales, and save it to the Power BI Desktop file. The best part is that we do not need to think about cleaning the Power BI file from any test artifacts. All testing and troubleshooting has been done in DAX Studio only.
Thank you for reading
That is all for this post. In part two I will show you how to view model metrics, benchmark your DAX and run a server timing trace on your DAX query .
I hope you enjoyed it and learned something along the way.
Did you enjoy this post or have other feedback? Please comment below!