Using DAX Studio or DAX Editor as add-ins to create measures in SSAS Tabular
Part two of a three-part series of blogs

The built-in DAX formula editor in SSAS Tabular leaves a lot to be desired. This blog shows how to install and use two of the most useful third-party add-ins: DAX Editor and DAX Studio.

  1. Editing DAX expressions - available tools
  2. DAX Studio (this blog)
  3. DAX Editor

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 11 January 2016

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.

DAX Studio

This is my preferred DAX editor, but that's mostly because I can't get the other one - DAX Editor - to work as it should.  Here's what DAX Studio looks like:

DAX Studio

Writing a query to show total quantity sold by product in DAX Studio.

If you're wondering why the DAX doesn't look familiar, that's because this is a query and not a measure - but DAX Studio can be used to write both, as explained later in this blog.

Installing DAX Studio

At the time of writing, you can download DAX Studio from this CodePlex site:

Download button

Click on this button to download the latest version.

 

When you run the downloaded set-up file, after the usual introductory questions you'll need to say what sort of install you want:

Excel install option

If you want to create measures (calculated fields) in PowerPivot within Excel, you should enable the Excel add-in too.

However, I've had problems getting the Excel add-in to run in Excel, and you won't need it for this blog.

Running DAX Studio

When you run DAX Studio, you'll be asked to connect to a SQL Server Analysis Services server:

Connecting to tabular server

I've deployed my models - as readers of this blog will know - to a named instance of SQL Server called sql2012 on my laptop.  Your machine will be different!

You can now choose which model you want to work with:

Connecting to a model

Click on the drop arrow next to the pin to choose from which model you want to pull columns and tables.

 

You can now create formula by dragging fields and tables in from the left-hand side:

Building a formula

Hold down the mouse wheel and scroll to change the font size (here it's a bit big).

You can also switch from Metadata to Functions to drag DAX function names in:

Including functions

You can drag function names into your DAX expressions.

 

Running a DAX Studio measure

The problem with DAX Studio is that it's mainly built for writing DAX queries (similar to SQL queries, but using DAX to extract data from tabular models).  It's not so useful for writing measures:

Running a DAX query

When you click on this button to run your "query", you'll just get an error.

The easiest thing to do is therefore to write your measures in DAX Studio, but then copy and paste them into SSAS Tabular.  However, don't dismiss this as too cumbersome without first reading about some of the extra DAX Studio features below.

Formatting DAX

One of the best features of DAX Studio (and certainly the one I used most when writing the Wise Owl SSAS Tabular course) was the Format Query button.  This allows you to take a DAX measure:

Unformatted DAX

Finding it hard to see what this measure does? Copy it to the clipboard.

You can then paste the selected DAX measure into DAX Studio:

Paste and format query

Paste in the measure, and click on the button shown to format it.

The results are much easier to read!

Formatted DAX

You can now copy this formula back into your measure.

The result of all this to-ing and fro-ing?

Formatted measure in SSAS Tabular

The measure is now easier to read!

 

Refreshing a model in DAX Studio

If you've made changes to your model, you can easily refresh it in DAX Studio:

Refreshing metadata

This handy button brings everything up to date.

 

Server timings

Although it only really works if you're writing DAX queries, the facility to time queries in DAX Studio can be very useful:

Server timings button

Click on the button shown to time how long queries take to run.

You can then run a query and see how long it took:

Timing a query

The message disappears when the query finishes, and timings appear.

 

So that's DAX Studio - next, I'll show how to install and use DAX Editor.

This blog has 0 threads Add post