Editing DAX Queries with DAX Studio

dax studio

Among the new features sported by Excel 2016 is the highlighting of DAX keywords in the Power Pivot formula bar.

Example of DAX formula highlighting in Excel 2016

(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.

Installation of DAX Studio

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.

Starting DAX Studio

You can start DAX Studio as a standalone exe from the Start menu.

DAX Studio icon on Windows 10 Start Menu

When you start DAX Studio in this way, it will ask you for connection information for an SSAS tabular mode server.

Connection dialog for standalone DAX Studio

We can, as mentioned, start DAX Studio from the dd-ins ribbon menu.

DAX Studio Button on Excel 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.

Connection Dialog for DAX Studio Excel Add-In

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:

Connection dialog recognizes if Power BI desktop is running.

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.

DAX Studio IDE

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.

Conext menu for DAX Studio output options

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.

Trace options in DAX Studio IDE

Conclusion

The DAX Studio is a top quality application that should be part of the toolbox of anyone who writes DAX queries.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.