560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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:
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.
At the time of writing, you can download DAX Studio from this CodePlex site:
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:
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.
When you run DAX Studio, you'll be asked to connect to a SQL Server Analysis Services 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:
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:
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:
You can drag function names into your DAX expressions.
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:
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.
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:
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 in the measure, and click on the button shown to format it.
The results are much easier to read!
You can now copy this formula back into your measure.
The result of all this to-ing and fro-ing?
The measure is now easier to read!
If you've made changes to your model, you can easily refresh it in DAX Studio:
This handy button brings everything up to date.
Although it only really works if you're writing DAX queries, the facility to time queries in DAX Studio can be very useful:
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:
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.
25 Aytoun Street