All,
Came across a really good trick last night that I wanted to share with you. For complex measures involving virtual tables, one of the best ways to debug them is via DAX Queries in either DAX Studio or Tabular Editor. A DAX Query starts with EVALUATE, and instead of returning a scalar returns a table.
In the measure below that I was debugging yesterday, I have highlighted the scalar variables in yellow and the table variables in green. You can see that the current query is set up to evaluate the _vTable variable, and does so in the results pane at the bottom of the screen. All good so far.
However, if you try to step through the variables sequentially, when you hit a scalar variable the query will return an error because it is expecting a table.
One way of handling this is to create an artificial table using the scalar in a ROW function, like this:
RETURN
ROW( “Wrst3”, Worst3 )
This works fine, but there’s an even easier way that I came across. Just use the squiggly brackets to turn your scalar variable into a tiny one cell table:
RETURN
{ Worst3 }
Which returns the following:
Being able to quickly and easily step through both table and scalar variables makes it much easier to identify and fix the source of your problems when you are getting unexpected results in complex measures.
I hope you find this helpful.
– Brian