Debug DAX (Thoughts about it)

[powerbirino3]

Hi guys

I've been working with Power BI Desktop for about 8 months a lot of hours (at work and at home).

Without doubt, the thing that has made me lose more time is making "difficult" measures. I quote difficult, because, they aren't super difficult calculations, but without a way to debug, sometimes it's difficult to know what calculations are being done behind and how.

For example, something so apparently "easy" as making a total in a matrix as you want, requires (maybe) using SUMMARIZE or CALCULATE or other combination of functions with several lines inside, and you have to imagine more or less, the input, the calculations inside, and you can only see the output result (if it doesn't give error before...). If you are a programmer (as me), this can be difficult to achieve, because if I want to do a simply measure where total would be:

total = total + measure

That simply line like that isn't possible to do in DAX (or at least I've never seen it or been able to do it. I know with VAR you can, but you can't aggregate)

Some of you maybe are thinking: "Well, you can do your calculations in a Calculated Table or in a measure and you can see the result in a table or in a KPI" (if that's the case)

Yes, sometimes it's useful, but that's useless if you need to keep the Context, without Context, that method is useless.

I googled about debugging DAX and the few results I've seen, they talk about "DAX Studio" or other methods, but actually it isn't really what I'm looking for.

So, my question is... is there any real way to "debug" DAX? With this I mean that, the moment I would put a measure into a visual (measures runs), you can see exactly what calculations are being done step by step in a program. Like if it were a debugger of any IDE, for instance, Visual Studio or PHPStorm.

Thank you!

[Phil_Seamark]

HI @powerbirino3

If you really want to get under the covers you can use DAX Studio and/or SSMS Profiler to extract the query plans.  These are not for the faint hearted, but if you are keen to invest the time, will provide excellent information to help you optimise your query. 

[ccakjcrx]

Hey @powerbirino3!

I'm not aware there a standard operating procedure for your problem.

I can tell you I've found some good resources. One is Curbal's YouTube channel. Ruth runs that channel, and she has a video that addresses your problem to some extent. Here is a link to that specific video:

DAX Fridays! #54: Reverse engineer/ Deconstruct a DAX measure

IMO, there isn't really a better resource that addresses the problem you bring up. However, an overall solution would be to purchase the training available on SQLBI.com. Like you, I spent endless days trying to gather info from different sources to come to a better understanding of row & filter context with DAX. I simply gave in and purchased the courses available on SQLBI.com. I cannot tell you how valuable the information is in those courses. There is a significant expense involved, but it is well worth it. 

Nothing in the Mastering DAX courses deals with debugging, or stepping through parts of DAX expressions to see how each part of the expression is being calculated; however, the content is so comprehensive it may give you the insight you need to save you a lot of time. 

As far as I'm concerned, there simply isn't better resources than vids posted by Curbal & SQLBI.com. The SQLBI guys train Microsoft staff, and present at Microsoft shows.

Hope this helps.