Using DAX Studio to edit calculated fields or measures in Excel
The editor for calculated fields in Excel (aka measures) isn't the greatest; this blog shows you how to install and use DAX Studio as a substitute editor.

Posted by Andy Brown on 20 February 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Measure/Calculated Field Editor Excel 2013 - DAX Studio

PowerPivot is a wonderful product, but its editor is its weakest point:

PowerPivot editor

Making expressions easy to read is difficult!

 

This blog shows how to download and get started with DAX Studio, a separate Excel add-in (it also works as a stand-alone product).

Wise Owl have no link to this software, and take no responsibility for it!  We do, however, use it to create our PowerPivot expressions.

Installing the Software

To install DAX Studio, go to this site and click on the DOWNLOAD button.

Getting the Add-In to Work

This is what you should see when you go into Excel (I'm using Excel 2013):

Add-ins tab on ribbon

You should see an ADD-INS tab on your ribbon.

Here's what you should see when you click on this tab:

Add-in - DAX Studio

You should see this icon when you click on the ADD-INS tab.

 

Customising your Ribbon

It's probably a good idea to move DAX Studio to a more useful place.  First choose to customise your ribbon:

Customising ribbon

Right-click on any part of the ribbon and choose the option shown.

You can now drag the DAX Studio icon to a better home:

Moving DAX Studio icon

Click on the DAX Studio tool as shown, and drag it to its new home.

The ADD-INS tab, being empty, should disappear when you return to Excel.

A sledgehammer to crack a nut

DAX Studio is designed for writing self-contained statements in the DAX language (which is a bit like SQL in this context).  For example:

DAX Studio example

Running this query would list out all of the rows in the Transaction table.

You can choose where the results will be listed like this:

DAX results to grid

Choose this option to show the results in a grid, in a similar manner to SQL Server Management Studio.

 

However, we're using DAX Studio just to format calculated fields in PowerPivot - very much a sledgehammer to crack a nut (another colloquialism, I'm afraid).

Creating an expression in DAX Studio

Here's how to create a formula to sum the value of transactions (to take one simple example).  First drag the function that you want into the window:

Using functions

Drag a function in from the list on the Functions tab.

 

Note the syntax expected:

SUMX expression syntax

Here we'll need to provide a table and an expression.

 

You can increase the font size by holding down the CTRL key and scrolling your mouse button (or use the drop arrow at the bottom left of the expression window).

Use the TAB and ENTER keys to start your expression:

Starting an expression

We'll indent all of the arguments for the function.

 

You can drag tables and columns into the expression editor:

Dragging in tables and columns

Here we're about to use the Quantity column in our formula.

When you've finished, select the results and press CTRL + C to copy them:

Copying an expression

You can also right-click to copy.

Paste this into a PowerPivot calculated field if you're using Excel 2013, or measure if you're using Excel 2010:

Pasting into Excel

The calculated field in Excel.

 

Excel will retain the indentation, but not the line numbers (and comments don't seem to work either).

Whether it was worth using the separate editor depends on a) how much you use DAX and b) how much you dislike the built-in expression editor in PowerPivot. 

This blog has 0 threads Add post