BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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.
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.
Installing DAX Studio
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.
Running DAX Studio
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.
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:
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!
Refreshing a model in DAX Studio
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.