In this writing, I want to share how I utilize DAX Studio to check & fix measures and enhance performance. This conveys what I do as a first step in DAX Studio, so it is simple to follow. I will share what are my second step and third step in my later writing.
There are a lot of things to check to enhance the performance of the report even before coming into DAX Studio, for instance, how data is extracted, transformed, & loaded, or how visualizations are designed on each page.
In this blog post, I will try to only explain how I fix the bad performance of DAX measures via DAX Studio in a first step.
I use the below sample data in order to describe how I proceed. The sample contains a million rows shown as below. The sample PBIX file is attached and you can find it at the end of this blog post.
Sales table
Datamodel
I wrote the below two DAX measures to describe two card visualizations and one table visualization on one page.
QTY total: = SUM( Sales[Quantity] )
Country count with Blue color: = CALCULATE ( DISTINCTCOUNT( Sales[Country] ), FILTER ( Sales, RELATED(Color[Color]) = "Blue" ) )
→ The measure, [Country count with Blue color:], is purposely created not in an efficient way in order to compare with a more efficient measure that is created later.
Turn on "Performance analyzer" in the View tab, and select Table visualization to copy query.
Open Dax Studio, and turn on "Server Timings".
As shown in the below figure, paste the query that was copied from "Performance analyzer" in Power BI Desktop (1), and then click "Run" (2). In order to see the result, click (3).
Click "Server Timings" in order to check the performance of the Table visualization.
As seen above, "SE Queries (Storage Engine Queries) 133" seems not right.
The result contains two DAX measures, so I check which measure is showing bad performance. In order to check the performance one by one, I need to comment out each measure and run only one measure at a time.
Two measures' performance are shown below, and it is clear that [Country count with Blue color:] measure has an issue.
Remain only the slower DAX measure, make some spaces after "Define", and then find the problem measure -> right click -> Define Measure.
The full DAX measure that I wrote in Power BI Desktop is shown together, and in here, I can fix or rewrite it to check whether rewritten measure enhances performance or not. Rewriting here does not influence the Power BI report, so it is easy to test the performance by writing DAX measures as many times in a different way as possible.
Like the below figure, comment out the initial DAX measure (1), rewrite the DAX measure (2), and then Run (3).
"SE Queries 2" is shown in "Server Timings", and it seems that it describes a reasonable performance for now.
Once the better performing DAX measure is checked, copy it and paste into the Power BI Desktop.
To summarize, in order to capture bad performing DAX measures,
Check which visualization shows bad performance
Copy query and paste it into DAX Studio
Turn on Server Timings
Check each measure's performance one by one by commenting out the rest of the measures
Find slow performing measures and click "Define measure", and then rewrite the DAX measure
Find the better performing one and copy-paste it into the Power BI Desktop
I hope this helps to have fun in analyzing measures in DAX Studio.
The sample pbix file is attached to the below.
Thanks for Share this. I love it.