BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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):
You should see an ADD-INS tab on your ribbon.
Here's what you should see when you click on this tab:
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:
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:
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:
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:
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:
Drag a function in from the list on the Functions tab.
Note the syntax expected:
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:
We'll indent all of the arguments for the function.
You can drag tables and columns into the expression editor:
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:
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:
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.