The blog has been a bit stagnant… even though I have quite a backlog of things to share. In my defense, I did create over 900 measures in a single Power Pivot model for a customer since we last talked. Though… that idea sounds so crazy I probably shouldn’t bring it up.
There has been a ton of changes in the Power BI landscape… I’m skipping that discussion. I have two different “Part 1” posts… without completing the corresponding Part 2. I’m skipping those, too. I was able to write an addin for Excel 2013 that lets me import measures to my model from a text file… and, I’m skipping that discussion, too. For today, we talk about my new love of DAX Studio.
Why New Love?
I have had DAX Studio installed a long time. It’s just… I haven’t really used it a ton. I would use it occasionally to dump all my measures out of my model (eg, so I could easily search for certain column references) or maybe to help debug a complicated measure. But that was really about it. However, as of version 2.2.0, the author of DAX Studio, Darren Gosbell unleashed (for me) its most awesome feature… Tracing (of query plans and server timings) against Power Pivot models! While this existed for tabular models… it wasn’t available for Power Pivot and there was just no convenient way to get that data. Data that I really really wanted sometimes.
Before I forget here are links to documentation on tracing Query Plans and Server Timings.
Let’s say we have the awesome table at left with 3 columns of numbers, and we need a measure to add them all up.. but only if all values are > 10000. We have a ton of ways we could write this measure… some examples:
SUM1:=CALCULATE(SUM(Table1[Value1]) + SUM(Table1[Value2]) + SUM(Table1[Value3]), Table1[Value1] > 10000, Table1[Value2] > 10000, Table1[Value3] > 10000)
SUM2:=CALCULATE(SUMX(Table1, Table1[Value1] + Table1[Value2] + Table1[Value3]), Table1[Value1] > 10000, Table1[Value2] > 10000, Table1[Value3] > 10000)
SUM3:=CALCULATE(SUMX(Table1, Table1[Value1] + Table1[Value2] + Table1[Value3]), FILTER(Table1, Table1[Value1] > 10000 && Table1[Value2] > 10000 && Table1[Value3] > 10000))
Of course, we are pretty darn curious which method (if either) is faster. Cuz we love performance. Who doesn’t!?
This is really easy to test out with the new DAX Studio features!
A bit of “How To”
I just launch DAX Studio, and write the following code: evaluate row(“SUM1”, [SUM1])
Which… is a bit weird if you have never played in DAX Studio or SSMS, but isn’t complex. “evaluate” just dumps the contents of the table passed as an argument. ROW( ) is a function that returns a table from a caption and expression – in this case our [SUM1] measure.
Before you run it, simply click the options for Query Plan and Server Timings. That way, once you run the measure (using F5), you will get some new tabs. The Query Plan is probably only useful for the true ninjas, but I like to keep it there to feel smart 
However, the Server Timing are awesome.
Total shows you the total time in milliseconds, so… that’s obviously darn useful, and how we will see that [SUM3] is the fastest (which may or may not have surprised me). On the left, you are really hoping for your time to be spent in SE (Storage Engine) rather than FE (Formula Engine) since the former is wicked stupid super fast… so consider re-writing queries to encourage less FE usage.
On the right you can see the steps taken, and how long each took. You can click on the steps to see the SQL that got executed. This will really help you understand how your measure is being evaluated, and hopefully give you clues on how to re-write it.
Thanks and Warnings
First, thanks to Darren. For a performance geek like me… this functionality is just huge. It’s roughly a million times easier than trying to capture a profiler trace! Secondly, please try not to infer too much off my stupid tests above. My data was purely random… real data isn’t like that! There is only 1 way to know the best way to write a measure for your data, and that is to measure it. With DAX Studio!
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Scott,
which version of Excel are you using? I’m curious because the GUID you have in the name of the table should be removed from the xmSQL code in the profiler and I wrote that code, so in theory it’s my fault but I should be able to fix it… 🙂
Thanks!
Marco
“… import measures to my model from a text file… ”
Talk about a tease!!! Please do tell – and SOON.
I too would be very interested to hear about your add-in that imports measures from a text file into Power Pivot.
Thanks for sharing Scott. I remember Marco saying something about this feature when it was first released. I now vaguely remember trying to install it but something went wrong – can’t remember what. As a result I forgot about it and have never used it. Your blog has reminded me and I now want this. Off to Codeplex now 🙂
Are you planning on providing more details about your technique for importing or publishing your add in for purchase or gratis. Thanks