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
- #314 Added 3 Quick Access buttons for
- New (with current connection)
- #277 Added Export button to all trace outputs
- Added full filename tooltip to tabs (thanks @dmarkle)
- Promoted View Metrics (Vertipaq Analyzer) from preview status to general availability
- Promoted Export Data feature from preview status to general availability
- Documentation Updates:
- Added license page
- including a section on SmartScreen issues in Win10 (thanks to Gilbert at fourmoo.com )
- Updated syntax highlighting to align with DaxFormatter.com
- Added a note in the Database tooltip that the Database Id can be copied using a right-click
- Added formatting to shorten asazure: and powerbi: server names in the status bar so that the key information is visible
- Added a partitions tab to the Model Metrics views
- Added a sample of any missing keys to the relationships tab in the Model Metrics (these keys are not saved for privacy reasons when exporting to a vpax file)
- fixed cancelling of exports to SQL Server
- improved keyboard navigation by adding IsDefault/IsCancel properties to dialog buttons (thanks @dmarkle)
- fixed an issue with intellisense not re-enabling after reconnecting (thanks @dmarkle)
- fixed an issue with Query History pane not updating the “Current” Database filter when changing databases
- disabled external tools when connected to PowerPivot
- #290 updated all URL references to use https (thanks @warthurton)
- #291 fixed issue connecting from Excel 2010
- #301 refined the code completion to prevent it overwriting code when editing in the middle of an expression
- #302 disabled column re-ordering in Metrics view
- #303 fixed an issue with the metadata pane when connecting to a model with dynamic format strings defined in calculation groups
- #308 removed backtick characters in column names with spaces where using the Static Excel output
- #320 fixed server not found error when exporting vpax file for PowerPivot models
- #325 fixed error when attempting to connect to PowerPivot files stored on OneDrive
- #329 fixed incorrect database name shown when launched from the Excel addin and not connected to PowerPivot
- #330 fixed the status message getting stuck after writing output to a file destination
- #339 fixed a bug that reported an assembly load error when Cancelling a query
June 9, 2020 at 2:26 pm
Great, thank you
June 9, 2020 at 8:15 pm
Hello Darren, the Query Builder and Query Benchmark is awesome. Thank you very much.
June 9, 2020 at 8:45 pm
Very nice addition to DAX Studio. I have a couple questions on why you implemented a few things the way you did with Query Builder.
First of all, why did you choose to use SUMMARIZECOLUMNS when that function generally cannot be used in any measures because it doesn’t support a context transition? Therefore, we won’t be able to paste queries we create into our DAX expressions or measures.
Secondly, why did you choose to use FILTER for filters created instead of CALCULATETABLE, which generally offers better performance.
Nice work and thank you for DAX Studio.
June 10, 2020 at 2:48 pm
The query builder follows a similar pattern to the queries generated by the table visual in Power BI. The whole point is that it generates a query which will run one or more measures in a view similar to the table visual in Power BI so that you can see how your measure works over a number of different rows. If you want to test a measure expression inside a query you can click the ADD button in the top right of the builder and add your expression there or click the edit pencil on an existing measure (and in that context your are correct that SUMMARIZECOLUMNS should not be used). It’s the expression you get when you click on the little “edit” pencil that you would copy back into your model, not the entire query.
I’m not sure where you got the information on CALCULATETABLE offering better performance than FILTER. Are you able to point to the source of that information? The predicates inside a CALCULATETABLE expression actually get compiled down to a FILTER expression. So when you type CALCULATETABLE( VALUES(
), = “Value”) it actually gets evaluated as CALCULATETABLE( VALUES( ), FILTER(ALL( ), = “Value” )
June 17, 2020 at 1:51 am
Great tool Darren, thank you very much for sharing
I tried to take inspiration from Dax Studio add-in source for a document level VSTO of mine, where I’d like to read from PP data model embedded in Excel through ADOMD, but unfortunately it’s too complex for me…
Can’t find dlls on my PC with Excel (Microsoft 365 click to run) and always getting errors trying to open a connection, like “No such host Is known”, “The ” local cube file cannot be opened”…
Hopefully, do you have any hint to point me in the right direction?
Sorry for asking here, and thanks anyway
June 17, 2020 at 2:04 pm
It’s hard to comment on source code I cannot see. But this is a really complex piece of code as you need to dynamically bind to the version of AdomdClient that is loaded by Excel. You cannot use the AdomdClient from Nuget to talk to PowerPivot.
June 17, 2020 at 3:30 pm
Thank you for taking the time to reply, Darren