Among the new features sported by Excel 2016 is the highlighting of DAX keywords in the Power Pivot formula bar.
(Just as an aside, this example definition makes use of the new DAX variables.)
Any help writing DAX is greatly appreciated, but while the colorization is a nice feature, it would be nicer still to have a full-fledged DAX editor. We can have this today, if we are willing to look to codeplex.com rather than to our friends in Redmond. The codeplex project DAX Studio provides a terrific DAX editor with dual functionality. You can run it as a standalone editor and edit queries for a tabular mode database, or you can use it as an Excel Add-In. When run as an Excel Add-In you can still query a tabular mode database if you wish, but you can also query the power pivot database (or “data model” if you prefer) embedded in the open workbook. The DAX Studio can also be used to edit queries for Power BI Desktop.
The setup exe file for DAX Studio can be downloaded here. There are prerequisites, including the Visual Studio Tools for Office, but if you are using Power Pivot the requirements are likely to be already covered.
The website mentions Excel 2010 and Excel 2013; I installed for Excel 2016 without difficulty.
You can start DAX Studio as a standalone exe from the Start menu.
When you start DAX Studio in this way, it will ask you for connection information for an SSAS tabular mode server.
We can, as mentioned, start DAX Studio from the dd-ins ribbon menu.
When used as an Excel Add-In, the DAX Studio enables the Add-Ins ribbon if it is not already enabled and places a single icon there. Clicking on the rather lonely-looking DAX Studio button reveals a Connect dialog with a second option.
We can, and most likely do want to connect to the “Power Pivot Model”, the database integrated into the current workbook.
As an added benefit, if you are running Power Business Intelligence (BI) desktop when you start the DAX Studio, it will recognize that fact and offer another connection alternative:
Once you have selected the appropriate connection, you can enter a DAX query and hit the expected green arrow to execute it. By default, DAX Studio retrieves results into a grid.
The DAX Studio provides several additional output options. Like the Management Studio, it can send results to a file. However, if you have started DAX Studio as an Excel Add-In, you also have the option of returning the results to an Excel worksheet. This worksheet can either be linked to the tabular data source or it can be a static copy of the rowdata returned to the Studio.
As a further advantage, if you are retrieving results from a tabular mode server the DAX Stuio makes it convenient to create and access trace information without involving other tools. The query plans are neither as pretty nor as easy to read as the query plans for a relational SQL Server query, but if you are concerned with the performance of your DAX queries, this information can be critical.
The DAX Studio is a top quality application that should be part of the toolbox of anyone who writes DAX queries.