The 2.11.0 release of DAX Studio is now available and brings with it the following new features and fixes.
New Preview Features
There are 2 new preview features this month, so you need to go into Options > Advanced and enable them if you want to use them
When enabled, the Query Builder appears as a button in the main ribbon
It lets you drag and drop columns and measures to build up a query which can include basic filters. You can also add custom measures or override the expression of a measure from your data model.
You can either run the content of the query builder directly or you can click the “Edit Query” button to send the text for the query to the main editor window where you can run it or further customize it.
The Query Benchmark tool appears as a button on the Advanced ribbon. It allows you to run a given query a number of times both against a cold and warm cache. This is useful because even on a quiet development server there can be a number of factors that can cause variability in the server timings.
The Benchmark feature makes use of the Server Timings functionality to record detailed information about each query execution.
You get the option of how many runs of cold vs warm cache (and by default these are linked)
The output of a Benchmark run shows a summary view with the Avg, StdDev, Min and Max of both runs for the Total Duration and the Storage Engine Duration
The detailed output shows the timings of every single query execution.
In addition to the two big features above there are a number of smaller features that have been added in 2.11.0
Have you ever run a DAX query from DAX Studio (or using a DAX window in SSMS) and wondered why the format you set on a measure does not always seem to get applied?
Let’s start with the following simple DAX query which simply lists the month number from 1 to 12 and a measure.
,"Internet Total Sales"
, [Internet Total Sales]
If we run this in DAX Studio you will see the following:
Note how the Format of the measure is correctly applied to return the dollar sign and the thousand separator and only 2 decimal places.
Now lets run the same query against the same model using SQL Server Management Studio (SSMS)
Now we have no currency symbol, no thousand separator and 3 to 4 numbers after the decimal place. What is going on here?
Well I’m going to let you in on a little secret about DAX queries:
The results in a DAX query are always returned unformatted.
You may well ask “Why is the formatting working in DAX Studio then?“. The answer is simple, I’ve specifically added code that looks at the column names returned by a query and then looks for a measure with the same name and applies any format string it finds.
You’ll notice in the example query that I’m setting the column name to the same name as the measure. If I change the column name to “AAA” you will see the following output.
Which is the same “raw” format we see from SSMS.
And if we exploit this for evil purposes we can even change the column name in the output to match a completely different measure. In the screenshot below I am applying the “Margin %” format to the [Internet Total Sales] measure so that it has one decimal place and a percentage sign and the decimal place is shifted two points to the right. I can’t think of a practical use for this behaviour, but you may see it occasionally if you are editing a query and change the measure reference without updating the column name.
You usually never see this in a client tool like Power BI as it builds the DAX queries internally so it knows which measures map to a given column in the result set so it can then apply the formatting appropriately.
If you’ve been following along with some of these example queries there is one other formatting feature we have in DAX Studio which you may have run into and that is the “Automatically Format Results” setting under File > Options.
This is off by default, but if you switch it on DAX Studio will apply some basic formatting based on the data type of the column in an attempt to try and make the results easier to read.
If the column is an integer use the format string “#,0” (this should include the appropriate thousands separator based on the language settings of your pc)
If the column is a decimal use the format “#,0.00”
If the column is a decimal number AND the name includes “pct” or “%” then use the format “0.00%”
This formatting of results is just one of the many small ways that I try to improve the user experience when working with queries in DAX Studio.
I’ve just released the 2.10.2 update to DAX Studio.
Starting with the 2.10.0 releases the crash reporting has been improved as there was previously an edge case where certain types of crashes were not triggering the crash report dialog. So this release includes a number of stability updates that have come from people submitting crash reports.
There are also a few smaller features that are detailed below
It seems that the last few years I’ve always seemed to do a pre-Christmas release for DAX Studio and this year is no different. The latest update of DAX Studio has just been released which includes a number of new features and bug fixes which are outlined below.
When you hover over the database dropdown in the metadata pane we now display an informational tooltip which includes the compatibility level of the database, the culture of the database and the date the data was last refreshed.
A couple of new settings have been added File – Options screen
The first lets you configure whether the editor inserts tabs or spaces when you indent code. This can be useful if you use other code editors and wish to keep a consistent style across them all.
The second lets you configure a custom export format by specifying a delimiter to use and whether or not to always quote string fields (however if your strings include your delimiter field they will still be automatically quoted as not doing this would not allow the file to be consumed properly).
This configuration appears as an option when you save query results to a file target or when you use the Export Data Wizard
A new Summary Pane has been added to the Vertipaq Analyzer view. This page gives the user a single place to see summary information about the model including the total number of tables and the total size of the model.
The tables pane now has a new column that indicates if any of the relationships for that table have RI violations. This uses the DMV that Phil Seamark pointed out in his recent blog post
You can now also import metrics that were previously exported
Date Table indicator
The metadata pane has been updated to indicate if a table has been marked as a “Date” table.
Ribbon buttons for Issues and Feature requests
I have seen a few issues posted in various other forums like the Power BI community forum over the years so I’ve decided to add buttons to the help ribbon to allow a quick and direct way of posting issues directly to our github issues list (although a github login is still required)
Search for Functions and DMVs
Both the Function and DMV panes now have a search box.
And in addition to the above features it includes numerous bug fixes including the following:
#224 Server Timings and Query Plan traces now work with the EffectiveUsername connection string parameter
There is an ongoing issue with some people getting an error from the Excel Add-in reporting a MissingMethodException which is preventing connections to PowerPivot models. However I’ve double checked a copy of the dll in question and it does contain the method so this error does not make any sense. And I have been unable to reproduce this issue on any of the systems which I have access to. If anyone is able to assist with more information on how to reproduce this issue I’d love to hear from you as I’d like to get this issue fixed.