I have learned something new about DAX Studio
watching the SQL Saturday Auckland 2020 event.
The online event started at 8 AM according to the time zone of New Zealand but the presenters and attendees were from around the globe living in different time zones.
In the GMT+1 time zone the event started at 22:00, I could not decide how to solve this problem. The two simple solutions would have been to completely ignore the event or to stay up all night. I ended up doing something (dates)between.
I watched the first session,
Introduction to Numeric Calculations in DAX from
Markus Ehrenmueller-Jensen who was a familiar face from the SQL Saturday Vienna 2020. Marcus talked about Numeric Calculations, while also explaining iterators, filter context, row context and context modifier functions.
After the session I went to sleep. For some reason I woke up at 5 AM the next day and decided to watch 2 sessions I was interested in.
Getting the most out of DAX Studio from
Darren is the original author and the maintainer of DAX Studio.
I was familiar with most of the topics but there were some features I have never tried such as the query builder. Darren also showed us the View metrics button which is one of the functions that were only available using another tool called Vertipaq Analyzer before the 2.11.0 version which was released in June 2020.
At the end of the session Darren answered my question regarding differences in server timing results.
When I am running queries against a local Power BI desktop’s data model I close every non-essential program on my computer that could affect performance but because we are talking about milliseconds and CPU usage the results are still slightly different every time. Darren recommended using a new feature called Run Benchmark.
It will run the DAX query multiple times. Afterwards you can check the details of every run and a summary with statistics over all the runs. This feature is currently in preview so you have to turn it on in the options.
The next and last session I watched was
My Power BI Report is Slow: What Should I Do? from
It was the third time that I watched an iteration of this presentation over the last couple of years but I am glad I decided so. I recommend watching Marco’s presentation not only to refresh your memory but because DAX Studio is becoming more and more helpful in finding the cause of performance problems.
When you are using the run benchmark feature it’s important to consider where your data model is. Is your data model local to your machine or are you connected to one that is concurrently used by a number of users?
DAX Studio is not something that only advanced users should use. There are a lot of ways to calculate the same values with DAX.
Using DAX Studio you can test the code from the books and posts you read and see the differences between them
not only in calculating the right numbers but in their performance also.