In this article I'll introduce xLDAX's in-built DAX editor.

Why another editor?

First off, if you’re looking for a more fully featured and powerful DAX Editor, you’ll need this: DAX Studio. I use both it, and the xlDAX editor, as although DAX Studio is very good, the xlDAX editor has a few neat tricks that make it useful for development, testing, documentation and for use in the field (i.e. no need to install).

But, more importantly, I use xlDAX's editor because it’s a UI wrapper for the core xlDAX user-defined-function (aka UDF) : DAX().

To activate the editor ...

...either right-click, xlDAX->DAX Editor or select from Ribbon Add-in Tab->xlDAX ->DAX Editor. You’ll be presented with something like this:

Alt text

What will appear on the DAX canvas depends on workbook selection in force at the time.

If the active cell on the workbook falls within a table, the editor will do one of three things:

  • If the table has a 2013 style data query command text associated with it, it’ll assume its DAX code and load it.

  • If the table’s top-left-hand cell contains either the phrase EVALUATE or DEFINE, it’ll load the full table (excluding headers) as a DAX Query (blank cells will be converted to single space indents).

  • Otherwise it’ll load whatever text is in the current cell and wrap it with and EVALUATE CALCULATETABLE expression. This also will be the case for a normal cell outside a table, unless it contains certain DAX generating xlDAX functions, but that’s for another day.

The Load DAX button will also follow the above rules, but also offers the ability to manually select a range of contiguous cells to load from.

Editing a DAX Query

Alongside the fairly limited editing abilities of a VB form text box, the editor offers four editing buttons:

  • Format DAX will attempt to put a pretty face on DAX code, no colours or such, but does an OK job.

  • Flatten DAX will remove all line breaks and indents.

  • Insert DAX will allow code to be loaded as per the Load DAX button, but rather than clearing the canvas, will insert wherever the cursor currently is set.

  • Clear will clear the canvas.

There’s also a DAX History tab which keeps a list of this session’s previously executed DAX Queries. Double-click to load back onto the editor’s canvas.

Running a DAX Query

The Execute button will pass the canvas text to the xlDAX user-defined-function: DAX() to execute the query.

If an error:

The error text will appear in the Result box. However, if Excel 2013, the only error info you’ll receive is a useless generic top-level ADO Error #-1055719414 message (Note: Excel 2010 will give a proper error). This seems to be due to Excel 2013’s hacking of the ADO object to support native Power Pivot models, the hack seems to have broken the ADO error stack.

To get around this, you can place the HAMMER add-in alongside xlDAX and it’ll then execute the DAX text via ADOMD’s .NET-only interface which gives back a proper error message. For another way around this, see the Paste DAX button below.

My most common syntax error with DAX tends to be a misplaced closing bracket, to help with this, there’s a Match ( button: place the cursor before an opening bracket and it’ll find the related closing bracket.

If a successful DAX Query execution:

The editor will paste a singleton response (i.e. the top-left-hand cell, below header line, of the result table) to the Result box (along with the DAX Query text). To fetch the full table, you’ll need to Pick a range to paste result to …. Just select a single cell and the resulting table to be pasted with the cell as its top-left-hand corner. If, however, the range selected, resides within an Excel table, the result will reuse the table’s range and name (but Note: the table will be deleted and renamed in the process, which will result in #REF errors in any dependent formulas!).

Saving the Query

The other thing you’ll likely want to do is paste back any changed DAX Query code to the workbook. The Paste DAX button will prompt for a range to be selected and depending on the nature of the range will:

  • If a single cell, not within a 2013 Query table or host to certain DAX generating xlDAX functions (will cover this later), the code will be pasted to that cell.

  • If the range is a multi-cell column the code will be pasted line by line, with a line per cell; you don’t need to select the required number of lines (but be careful as it will overwrite anything on its downward vertical path). This form of pasting is useful for documenting DAX queries, line by line.

  • If the table is a 2013 DAX Query table the command text will be replaced with DAX code and the table refreshed. (This is also a way around the ADO Error stack problem above, as query will return a proper error text in case of failure.)

Not just DAX!

The editor (via DAX()) will execute not just DAX Query code, but also DMV (such as “Select * from [Model].[$InvoiceHeaders]) and MDX. In fact, if the cursor resides within a PivotTable during a Load DAX event, the pivot table's MDX, if any, will be loaded onto the editor’s canvas (so no need to install OLAP Pivot Table Extension, if, like me, you normally just use it for extracting pivot table MDX code!)