DAX Studio is a fabulous free tool that allows you to directly query your Power Pivot/Power BI data models.  It is not immediately obvious (particularly to beginners) exactly how using DAX Studio can add value.  This blog post today is a simple reference to help anyone that uses Power Pivot get started with DAX Studio.

But What Can I Do With DAX Studio?

I’m glad you asked – lots of things – much more than I will cover here today.  But here are a few things that you can do that are really useful:

1. Extract a list of your measures from your data model into a spreadsheet
   • for looking at the big picture
   • for documentation
   • so you can more easily reuse the measures in another workbook without having to go hunting for them
2. Help you write more complex DAX formulas that contain “tables” as part of the formula.
   • When you write a measure that contains a table function in DAX, you can’t actually “see” the table to check if it is returning the table you expect.  In DAX Studio, you can write just the Table portion of your formula so you can actually “see” the table that is produced.  This makes it a lot easier to work out what you are doing, work out what is wrong, and hence solve problems you are having when writing formulas.
3. Learn how to write DAX Queries
   • It may not be immediately obvious, but once you build lots of business logic into your data model, there could be times when you just want to get a table (or list) of data and extract it to use for other purposes.  You could use a pivot table for this, but it can be better to write a query over the data model and extract the data you need into a table in some instances – particularly if the table is large.
4. Test performance of your measures
   • When a measure (eg in a pivot table) is really slow, you can run the measure in DAX studio and use the server timing tools to see how Power Pivot is interpreting your formula.  With this information you can set about re-writing the formula to be more efficient.
5. Use a Power BI Desktop PBIX file as a SSAS Server (kind of)
   • There is a trick that allows you to use Power BI Desktop as a server and then connect a thin Excel Workbook to this “server”.  It only works for the life of the session, but it is still cool and could be useful.

It is important to note that DAX Studio ALWAYS returns a Table – there is no other choice.  This is exactly opposite to a measure in a pivot table which ALWAYS returns a scalar value.  You can use this fact to your advantage to help you debug measures that contain tables as part of the formula (which is hard to do in a measure/pivot table).  And there is a trick to allow you to return a single measure scalar value as a table – more on that later.

OK, enough already!  Let’s get started.

Downloading and Installing DAX Studio

The latest version of DAX Studio can be downloaded here.  This is not one of those predatory websites that tries to trick you into downloading some software you don’t want/need.  Just click the big purple download button on the first page and you are away.

After downloading, just run the install routine.

Connecting DAX Studio to Excel Power Pivot

The first step is to launch Excel and open an Excel Power Pivot Workbook.   Once you have your Power Pivot Workbook open, you will notice that there is a new “Add-ins” menu on the ribbon (shown as 1 below).  Click on the DAX Studio button (2 below) to launch DAX Studio.  Note on my menu below that I have another tab for Power Pivot Utilities.  Why not go ahead and install that now too – it is a great tool that you can read about here and it makes one of the tricks I am going to show you further down the page much easier to execute.

When you launch DAX Studio, you will be prompted to connect to a data model.  The default is a currently open Excel workbook (when you launch DAX Studio from within Excel).  Don’t worry about the advanced options, just click connect.

NB: Note above how there is an option to connect to Power BI Designer (now called Power BI Desktop) to a file called “Test”.  This is because I also happen to have Power BI Desktop running with that file open.

After connected to the data model, you will see in your Windows Task bar that DAX Studio is running as a separate application – much in the same way that Power Pivot runs as a separate application to Excel even though you launch it from Excel.

Connecting DAX Studio to Power BI Desktop

When you open Power BI Desktop, you will not find a button to launch DAX Studio like you do in Excel.  You can however launch DAX Studio from your Program Files from within Windows.  If you have Power BI Desktop open when you launch DAX studio, you will get a connection dialogue as shown below.  Just select the correct data model for the open Power BI PBIX file.

Note:  You can also use DAX Studio to connect to SQL Server Tabular, however that is out of scope for this post.

DAX Studio Window – Getting Started

Now you know how to connect DAX Studio to your data models, it is time to look at what you can do (I will be using Excel as my source).  There is a lot to DAX Studio, but let me call out a few things in the UI to get you started (Referring to the image below).

1. This is the list of tables in your data model – it should be familiar to you
2. This is the query pane – it is where you write your queries
3. This is the run button to execute your query (you can also press F5).
4. This is the default output pane – where you will see the results of your query
5. You can change the default output from the output pane (4) to various other alternatives including Excel or a file (CSV or TXT).
6. Once you write your query in the query pane (2), you can click this button to use the DAX Formatter service to format the query directly in the query pane so it is easier to read.
7. The server timings button is used for performance testing – more on that later
8. The connect button allows you to repoint DAX studio to a different data model.
9. Down the bottom of the page you can change from the list of tables to some of the other tabs including Functions (to help you write DAX) and DMV (Dynamic Management Views).  DMVs are a set of technical queries that will return you information about your data model.  More on that later too.

Extract a list of Measures

The first thing I am going to cover is how to extract a list of measures from your workbook.  There is a DMV for measures that is really easy to use.  Simply do the following:

1. Down the bottom of the DAX Studio window, click on the DMV tab.
2. Scroll down the list of DMVs and find MDSCHEMA_MEASURES towards the bottom of the list.
3. Click and drag the DMV and drop it in the Query Pane window as shown below.

When you do this, DAX Studio will automatically write your first query for you (shown as 1 below).  NB: This is not a DAX query but a SQL query.  You can then run the query by pressing the run button (2) or better still just get used to pressing F5.

Take a look at what you get down the bottom of the screen in the Output tab (1 below) and the Results tab (2 below).

The results tab (shown below) is simply a temporary storage location for the table returned by DAX Studio.  In fact the resulting table from this DMV is too large to fit on the screen (but you can use the scroll bars to see the rest of the table).

It is possible to change the output location to Excel as follows.  At the top of DAX Studio, change the default output (shown as 1 below) to Excel Static (2 below).

Once you run the query again (press F5) the results get sent to your Excel Workbook.  There is a lot more information provided in the output than you are likely to need.  In my sheet below I have only kept columns D and N.  Note how the first 14 rows in my model are not useful, but from row 15 you can see a list of the measures and formulas.  Very helpful for documentation and also for copying measures to other workbooks.

Now you can manually delete the columns and rows you don’t need to create a nice clean list of measures, but why not download and install Power Pivot Utilities like I mentioned earlier.  Power Pivot Utilities uses DAX Studio in the background to extract the measures (and other things) automatically for you, clean up the list and put it in a worksheet which is all nicely formatted – much easier-.  You must have both Power Pivot Utilities and DAX Studio installed for this to work.

• Click on the PP Utilities menu (shown as 1 above).
• Click “List Measures” (shown as 2 above), you will get this.

• Then why not go one step further.  Select (highlight) the DAX Formulas in the table that you want to format, then click “Format DAX expressions”, then you will get this – super!

Edit:23 Feb 2015.  Thanks to a tip from Mike Rudzinski in the comments below, here is a small SQL script that you can cut and paste into the DAX Studio query window and get a tight extract of just the measures.

select 
   MEASUREGROUP_NAME, 
   MEASURE_NAME, 
   EXPRESSION

from $SYSTEM.MDSCHEMA_MEASURES

where MEASURE_AGGREGATOR = 0

order by MEASUREGROUP_NAME

Extracting Tables from your Data Model

The next thing I will show you is how to use DAX Studio to extract a table of data from your data model.  In the most simple use case, you can simply extract a full existing table – not very useful unless you don’t have access to the source data, but still good as a learning exercise.

Extracting an Existing Table

Every DAX Query must start with the EVALUATE keyword.  The query below simply uses EVALUATE followed by the name of one of the tables (Product in this case).  It is good practice to use line breaks in the query pane to make your DAX Query easier to read.  DAX studio has very good Intellisense, code highlighting, and you can also use the tab key to space out your queries – hurray!

Just as before, you can swap the output and send the results to Excel (shown below).

It is also possible to extract the results to a CSV or Text file.  Unlike Excel, there is no 1,000,000 row limit on these text files, so you can use this to extract tables with millions of rows to a file.

Creating an Extract for a “New” Table in DAX Studio

Something much more interesting is to extract a new table that you build using a DAX Query – a table that doesn’t actually exist in your data model but can be fabricated via a query and then materialised into a spreadsheet or CSV.  There are a number of regular DAX functions that return Tables including FILTER, VALUES, CALCULATETABLE, DATEADD to name a few.

In the example below, I use the VALUES function to extract a list of all the Product Colours from the Products table. When you use the VALUES function in a measure in DAX, you normally can’t “see” the output – see how useful this can be to “See” the results of functions?    Using DAX Studio in this way can really help with comprehension of DAX functions that return tables as well as debugging complex formulas (that use tables) that you are writing.

And the same can be done with the FILTER function. Below I extract a list of all Products that have a list price of $1,000 or more.

In addition to these regular DAX functions, there are some functions specifically designed to be used in DAX Queries, such as SUMMARIZE and ADDCOLUMNS.  For those of you that know some SQL, SUMMARIZE is similar to GROUP BY (note it is similar, but not the same).  In the example below, I am summarising the Calendar table and extracting a table that contains all the Year/Month combinations.

You can also add an ORDER BY clause to force the sort order of your output as shown below.  Note I have added the MonthNumberOfYear to the SUMMARIZE function too (a requirement if you want to use the column in the ORDER BY clause).

Now using the ADDCOLUMNS DAX function, it is possible to create a summary table leveraging the measures in your data model too.  Below is a table showing the summary sales by month (I added a ROUND function too).

But this is not a tutorial on DAX queries, just a tutorial on DAX Studio, so I am not going to cover everything about DAX Queries here.  There are lots of resources on the web about DAX Queries – some good links are provided at the bottom of this page.

Move your Query to Excel

Here is a trick I learnt from Marco Russo.  It is possible to write a DAX Query directly in Excel, and you can then use this as an alternative to a Pivot Table to display data from your data model.  Using DAX Studio as your authoring tool and then copying the query to Excel is a really neat way to get a long list of data into your spreadsheet.  To demo this capability, I will copy the query from DAX Studio above to the clipboard and then embed it direct in Excel.  Once you have done this, you can refresh the query without the need to use DAX Studio again.

Go to a blank worksheet in the Excel Workbook containing your data model.  Then

• Select Data (#1 below).
• Click on Existing Connections (#2 below).

• Click on Tables (#3 below).
• Select any table from your data model.  It doesn’t matter which, but probably better to select a small one.  I have selected the Product table (#4 below)..
• Click Open (#5 below).

You will be prompted like shown below.  The default setting at the top allows you to insert a TABLE instead of a Pivot Table.  Just accept the defaults and click OK.  This will add a proper Excel table that is linked to your data model into your sheet.

Then right click anywhere in the new table (shown as 1 below) and then click Table (2), Edit DAX (3).

You will see the following dialogue box.  Note that the command type (1) is set to “Table” and the Expression is “Product” – the name of the table.  It is possible to change the command type (1) to “DAX”.

After changing the command type to DAX, you can then paste the DAX formula copied from DAX Studio into the Expression box, then click OK.

You now have a proper Excel Table in a sheet that is directly linked to your data model.  You can refresh the table by right clicking the table and then select “Refresh”.

Evaluate a Single Measure

As I mentioned earlier, the result of a DAX Query MUST be a table – no exceptions.  So what if you want to return a measure?  Well it is quite simple – you use the ROW function to return a single row table as shown below.

The ROW function takes 2 parameters, the name of the column and the expression to be evaluated.  It then returns a single row table  with one or more columns (1 column in my case) and then returns the result.

Testing Performance of Measures

This is a good time to look at the performance measure capabilities of DAX studio.  When you click the Server Timings button in DAX Studio (shown as 1 below), you will see a new tab (2) that among other things shows you the total time spent in evaluating your query.  It is important that you clear the cache (4 below) before you test a query (unless you are trying to test what impact the cache has on your query).

When I execute the query again (after clearing the cache), you can now see the total elapsed time for both the Storage Engine (SE) and the Formula Engine (FE).

In short, the storage engine is the fast, multi-threaded, cacheable engine.  The formula engine is very powerful, but is single threaded and not cacheable.  Where ever possible, you want your DAX formulas to use the storage engine in preference over the formula engine.  This topic (optimising your DAX) is a massive topic in its own right and is not covered in this post.  There is lots of good stuff (like this video) at http://SQLBI.com , but keep in mind this is an advanced topic.

Use your Power BI Desktop Model as a SSAS Server

One last trick (that I also learnt from Marco Russo) is that you can use DAX Studio help you use Power BI Desktop as a SSAS server.  To see how this works, you must first connect DAX Studio to a Power BI Desktop PBIX file.

When you run Power BI Desktop, it actually runs an instance of SSAS in the background.  Then once you connect to Power BI Desktop with DAX Studio, you will notice in the bottom right hand corner of DAX Studio some information that tells you the address of the SSAS server instance created by Power BI Desktop.

Note that the address changes every time you launch Power BI Desktop (no doubt deliberately so, otherwise no one would purchase SSAS Tabular).

Armed with this information, you can create a new Excel workbook containing a Pivot Table that points directly at this server without the need to have the data model in the Excel workbook (aka a thin Excel Workbook).  To do this, open a new blank Excel workbook, then select

1. Data
2. From Other Sources
3. From Analysis Services

You will be asked for the Server Name.  Just use the information provided from DAX Studio.

Click through all the dialogue boxes just accepting the defaults before finally inserting a Pivot Table to the sheet.  You can then build a Pivot Table that can directly access the data model in the Power BI Desktop file without the need to have the data or the data model in the Excel Workbook.

As mentioned above, this trick only works while the Power BI Desktop file is open.  The server address only lasts for the life of the specific instance of Power BI Desktop while it is running.  If you close Power BI Desktop and then reopen it again, there will be a different server address.  If you want to reuse your Excel Workbook that was connected to a previous instance of this temporary SSAS server, simply do the following.

1. Run DAX Studio again and get the new address of the new instance (using DAX Studio as before)
2. Open Excel and refresh the Pivot Table.  This will throw an error (because the old server instance doesn’t exist).  Click No
3. You will then see the dialogue to enter the server address again.
4. Just update the details and then go through each step accepting the defaults as you did before.

Bam – your thin Excel workbook is working against the new instance of Power BI Desktop SSAS.

Learn How To Write DAX

If you found this article easy to follow and you learnt something, then may be you could benefit from my Power BI tutorials, books and/or Power BI training.

The books “Supercharge Power BI” and “Supercharge Excel” are written in the same style and will help anyone wanting to become a Power Pivot or Power BI ninja make rapid progress on their journey.

I had written “Supercharge Excel” using Excel 2016, and it is the second edition of my other book “Learn to Write DAX” which I had written using Excel 2013.

“Supercharge Power BI” can be considered as a sister book with the same contents but using Power BI Desktop interface.

Each of these books have plenty of worked through examples and practice exercises to cement your knowledge.

“Supercharge Power BI Online Training” course is a combination of:

1. Self-paced remote learning using one of my books – Supercharge Power BI or Supercharge Excel.
2. Weekly video based training with examples and demos to support the complex topics in the book.
3. Weekly live screen sharing Q&A sessions with me, Matt Allington.