DAX Studio for Power BI: Getting Started - Excelerator BI

DAX Studio for Power BI: Getting Started

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.

Download DAX studio Button Screenshot

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.

Connect DAX Studio to Excel Power Pivot

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.

promtp to connect data model with power pivot

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.

Apps Icons

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.

prompt to connect Power BI Designer

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.

Elements of DAX window with pivot

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.

Extract a list of Measures in power pivot

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.

Select measures

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).

Output Tab

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).

Results Tab

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).

Change Default Output

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.

List of Measures and Formulas

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.

Power Pivot Utilities

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

Table, Measure and DAX expression in Power Pivot Utilities

  • 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!Format DAX extensions in Pivot utilities

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

Power bi training public banner

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!

Evaluate Keyword in power pivot

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

Send Results to Excel (pivot)

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.

See results of functions

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.

Filter functions

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.

Summarize columns

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).

Order by function

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).

ADDCOLUMNS

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).

Existing Connections

  • 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).

Product Table

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.

Link Excel table to data model

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

Edit table with dax

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”.

Select Command Type

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.

Paste DAX Formula

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”.

Refresh table

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.

Use ROW function

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).

Test performance measure

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).

Total time of Storage Engine and Formula

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.

Connect to SSAS server

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.

image

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

Connect table to server

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

Name server

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 NoError
  3. You will then see the dialogue to enter the server address again.Enter 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.

Super Charge Power Bi training workbook

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.

Super Charge Power Bi Ad 2

76 thoughts on “DAX Studio for Power BI: Getting Started”

  1. This is an informative and helpful guide for those new to DAX Studio in Power BI. It provides step-by-step instructions on how to get started with the tool, making it easier for users to create and manage their data models effectively.

    1. Matt Allington

      I’m not in front of my PC, so can’t check, but I’m pretty sure there is a DMV. You can look for it, probably called MDSCHEMA_COLUMNS or similar. Look for the DMV tab on the left

    2. You can connect the PBIX file with DaxStudio and
      Write
      “EVALUATE
      SUMMARIZE(COLUMNSTATISTICS(),[Table Name],[Column Name])”

      This should give you the column name and Table names from each and every Table.

  2. Thanks so much for posting this – very clear instructions. Helped me to extract PBI measures with ease.
    Kudos to you.

    And thanks Mike Rudzinski for his script in the comments below.

  3. Estamos en el año 2022 , pero el contenido de este blog sigue siendo muy util . Yo tengo varios meses aprendiendo Power Bi en forma autodidacta y ahora es cuando entiendo mejor la utilidad de esta herramienta . Pienso que ahora es cuando mas valor tiene conocer DAX STUDIO seria interesante que pudieses actualizar al año actual . Gracias por enseñar .

  4. Is my data safe in DAX studio? Considering DAX Studio is an Open Source application i’m little concerned if any data & connections are secure.

    1. What connections are you worried about? To connect between DAX Studio and a data source (like Power BI Desktop or the Power BI XMLA Endpoint) DAX Studio uses the Microsoft supplied AdomdClient libraries. These are the same libraries used by Microsoft tools such as SQL Server Management Studio and Power BI Desktop itself to connect to tabular models. These connections both use packet level encryption and compression and if you are connected to a cloud data source like AzureAS or the Power BI XMLA endpoint the connections are further protected by HTTPS encryption (so you have both packet and transport encryption).

      If you are concerned about DAX Studio sending information externally we have options where you can turn off all external connections if you want (although this does limit some of the functionality and our ability to fix issues) and you can confirm this by running your own network traces. See https://daxstudio.org/documentation/features/privacy-settings/ I know we have people at financial institutions which use these settings to prevent any outgoing connections.

  5. Hi Matt.

    Thank you for the Post.

    I got an issue when running the query bellow in the dax studio, I need to return all the measures where another measure, v_ano IEL 2019 exists in the expression, but the operator LIKE does not exists.]

    SELECT measure_name
    ,measure_caption
    ,expression
    ,measure_is_visible
    FROM $SYSTEM.MDSCHEMA_MEASURES
    where expression CONTAINS( “%v_ano IEL 2019%”

    Can you help me please?

  6. АНДРЕЙ АГАНИН

    Hello.
    I use DAXStudio (2.14.1) as an Excel add-in in Office-365. I save a table (more than 3 million records) to a csv-file, all the data uploaded to it is framed with quotation marks (this ” symbol), as follows :

    “16012101_2030_2021-01-16. csv”, “11-2020″,”aptekamos.ru”,”0″,”0″,””,” LEK. DISCOVERY. AND BIOACTIVE. ADDITIVES”, “0”, ” 0″

    In the DAZ Studio settings: File-Option-Standart-Custom Export Format, the Quote String Fields field is unchecked. The comma delimiter is / Comma separated text file-UTF8 (*. csv).
    Please tell me how to avoid the output of quotation marks in the file?
    Thank you for your advice/help.

    1. In order for the settings in the “Custom Export Format” to be used you have to choose that as an option in the “Save As Type” setting when the output is set to File. If you just use the standard UTF-8 csv format all string fields will get quoted.

  7. Hi Matt, I just purchased your book “Supercharge Power BI” on Amazon. Look forward to learning more. Thank you.

  8. Very useful article. DaxStudio is very useful. Can we not convert a Table that is created with Dax, into Cube Formulas?
    The was we use OLAP Tools for Pivot Table and convert to formulas.

    1. A cube formula returns a single value. A DAX Table is just that – a table – and hence you can’t return it from a cube formula. You can return the values from the table just like any other table.

  9. Hi there Matt,

    Thanks for the great post. I am wondering if there is any way to test a single calculated column like you would evaluate tables and measures? I have some potentially CPU and memory heavy CC formulas that I would like to check one at a time.

    Cheers, Michael

    1. Hi Michael,

      If you would like to test the functionality of a calculated column, please have a look at the “ADDCOLUMNS” DAX function (https://docs.microsoft.com/en-us/dax/addcolumns-function-dax). When using DaxStudio, keep in mind that the table needs to read into the function when you are testing a calculated column, which is part of the ADDCOLUMNS function.

      As an example, if you would like to test a calculated column named “TestColumn” for a table named “TestTable”, you would use the following function in DaxStudio:

      EVALUATE
      ADDCOLUMNS (
      TestTable,
      “TestColumn”,
      TestTable[ExistingNumberColumn]*10
      )

      This is equivalent to selecting the “New Column” button under the “Modelling” tab for TestTable and inserting the following function:

      TestColumn = TestTable[ExistingNumberColumn]*10

      Hopefully this helps.

      Cheers!

      Diedrich

  10. Hi Matt,

    Your post is very interesting. I am training in DAX. Can I please have a copy of the Power BI file or the file(s) used in the above demo for DAX Studio?

    Thanks,

    Michel

  11. Can one filter a table by a static date range in DaxStudio? Something like “datesbetween([tbl],’1/1/19′,’09/30/19′)” I’ved tried a few variations and functions but it keeps telling me no (just downloaded tonight).

    img: http://prntscr.com/pm1xji

  12. Matt, Thanks you for this Blog and your books. I authoring some queries with DAXStudio, all of them Run and Results Ok, I confirm in Output pane, How to “authoring” this queries in PowerBI desktop (commonly I used), some other queries I copy and paste with New Table in PBI desktop, just change the first line [EVALUATE by name of table]… How can I transfer, send to, … DAXStudio to PBI desktop? or I need visual on?…thanks for your help

    1. Sorry, I am not 100% sure what you are asking. The only way to create a query (that returns a table) in Power BI is to use the New Table button – as you suggested. If you want to see the results in Power BI, you could add a table visual to the report and add the columns from the New Table into this report.

  13. Hello Matt,

    Thank you for this article, it was extremely enlightening. I am interested in learning more about your “Move your query to excel” section. Previously I thought without using PowerBI publisher for Excel the only way to get my model from PowerBI Desktop to excel was using the “From analysis services” option in Excel and connecting to the localhost which only allows me to import the data as a cube and this the output was only a pivot table.

    Can you tell me how you got your model into excel to be able to dump it as a table?

  14. Hi Matt,

    Thank you for this clear tutorial post. However when I followed the steps to “Click on the PP Utilities menu”, an error message popped out indicating that “This workbook does not have a model”. What was this message abut? Could you suggest me on what might go wrong ?

    Many thanks!

    1. I’m not sure what is going one. Maybe something has changed with PPU. Does this error occur as soon as you go to the menu? Previously it has been possible to use the formatting feature even for measures extracted from a different model.

      1. I’ve always had this same problem the last couple of years at least. Here’s how I made it work:
        1. Open PBIX
        2. Open Excel, then launch DaxStudio from the add-ins or PP Utilities tab
        3. In DaxStudio connect to the PBIX
        4. Having selected Excel static as output, run this SQL (a tweak to the one above)
        select
        MEASUREGROUP_NAME,
        MEASURE_NAME,
        CONCAT(‘:=’,EXPRESSION)
        from $SYSTEM.MDSCHEMA_MEASURES
        where MEASURE_AGGREGATOR = 0
        order by MEASUREGROUP_NAME
        5. In Excel (Home tab of ribbon) Format as Table the results
        6. Rename the third column to DAX Expression
        7. Keep/move selection on third column header you just renamed
        8. In PP Utilities tab of the ribbon click Format DAX expression
        There ya go. Phew. Well, at least it works for me.

  15. Hi Matt,

    Great article! stumbled upon this post when googling a solution for my problem.

    I have a situation here which I think I could potentially use this DAX Studio but I am not 100% though. So would like to hear your view here. I used PQ to cleanse and summarize a dataset which was subsequently loaded to an Excel file’s Data Model where I have built my power pivot tables. Is it possible that I can use a separate Excel file to build power pivot tables based on this Data Model? So it is kinda like Front End: Excel, Back End: Another Excel’s Data Model created by the PQ?

    Many thanks
    Gordon

  16. Steven A Weydert

    One of the most generous and important posts ever. The visuals and explanations are amazing.

    However, the last step in the equation for me is to export to a CSV file. The Output options are for Excel (but I have over a million rows) and File (which sends to a notepad text file). Where’s the option to send to a CSV file?

    1. The UI can change from time to time. You need to select the option to export to File. Then when you click on Run, you will get the file save dialog. The default is text file, but you can select .csv also.

  17. Hi Matt,

    After I’ve finished some analysis in PowerPivot, many of my end users still require the data behind the analysis. Based on who is requesting, I need to filter by one particular column, and then provide them certain columns out of my model with all of the data, I can’t summarize. Is that something possible in DAX Studio?

  18. Hi Matt, I installed the PP utilities, opened a Power Pivot Model, and I am trying to start DAX Studio from the add-in menu. Unfortunately DAX Studio is greyed out. I am using DAX Studio 2.8.1. Also I am not able to connect to a Power Pivot model when I run DAX studio standalone, this option is greyed out stating I should launch DAX studio from Excel containing a power pivot model.

  19. Hi Matt,
    Great intro.
    Is there any way to simulate a filter context or row context in DAX studio? Ideally what I would like to do for debugging purposes is to show, for instance, what the result of a FILTER operation is in the middle of a DAX expression. Imagine I have the following in a calculated column that I am debugging:
    ‘Date'[Test] =
    CALCULATE (
    SUMX ( Sales; Sales[Unit Price] * Sales[Quantity] );
    FILTER (
    ‘Date'[Date];
    DATESBETWEEN ( ‘Date'[Date]; MAX ( ‘Date'[Date] ) – 89; MAX ( ‘Date'[Date] ) )))

    and I want to see the real result of FILTER(), taking into account the current evaluation context. Or the result of the second argument of that filter operation (MAX(‘Date'[Date]). In other programming languages you could assign those to a variable at the precise moment of interest in and check it later. Can something similar be done in DAX, either through DAX studio or any other tool/technique?
    Thanks very much

  20. Being able to extract all the measures from an existing PBIX is fantastic. But is there any way to import them into a new PBIX? That would save a lot of tedious work of creating each one and cut-n-pasting the expressions.

  21. Hi Matt, thank you for a very helpful article! I downloaded and installed both Dax and PP Utilities, however I am not able to reproduce Steps 2 and 3 in order to obtain the List Measures. The Output in my DAX does not show Linked and Static Excel, but only one Excel which allows export as a csv.file. I have to re-save the csv as an Excel file and then create a model in PP. Any idea why my Output in Dax looks different and do you think this is causing the issue with List Measures? Thanks!

      1. Good Morning Matt, opening DAX from Excel worked up to the point of getting the measures in an Excel tab “DaxResults” . After that I added to Data Model and when I selected List Measures, I got a message _No Measures Defined :=1. I feel I am missing a step in between, but not sure what it is. I will keep trying, but if you have another suggestion I will greatly appreciate it. Thanks!

        1. Hi Matt, I have not been able to figure the List Measures issue out yet, but was able to resolve the issue with PP Utilities tab disappearing from the Excel 2016 Ribbon in Windows 10. I first tried the suggestion to unblock the ZIP file, but that was a no go. Here is what worked for me:
          1. Open Blank Excel sheet – File – Options – Add Ins – Manage Excel Add-ins – Go
          2. Make sure PP Utilities is selected – Click Browse
          3. Open the PP Utilities Folder
          4. Right click on the PP_utilities.xlam file to open Properties
          5. General tab – Unblock – Apply – OK
          Hopefully this will help others.

          1. Hi Matt, thanks for the compliment and you are most welcome! I really glad this would be useful. I am using 2016 Excel. Now I just have to figure out why List Measures is not working for me. Do you have to load the data into a Power Pivot Model after you run the DAX query and it dumps the data into Excel?

  22. Hi Matt,
    Thanks for posting this, it’s very helpful!
    I’m working on ways to document measures living in a Tabular cube. Do you know if it is possible to pump the outputs of a DMV query (for measure meta data) back into the cube?

    1. If you are using Excel, then yes. Load the DMV into a table in Excel, then add the table back to the data model using “Add to Data Model”. I don’t know how you can do this with Power BI however.

  23. Hi Matt, Thanks for writing the user’s manual for DAX Studio. I’ve been looking for a while for a resource like this. Keep up the good job.

  24. Thanks Matt, Great post as normal…I had one issue however when i try to Output to Excel nothing happens….exporting to the Grid shows the data and records. CSV export is fine but the Excel option doesn’t show the dialog box for Linked Excel or Static…? Have you seen this before, maybe bug?

    1. I’m not sure if it is a bug or a change in the design. I note that there is an option in the “Output” to select All, Standard or Excel. When I select Excel, there is nothing there. So it’s either a bug or a change – I am not sure which.

  25. Thanks for the article!! When you connect to the power bi it says “the file:\my data sources\localhost_58156 d906e7bd-417f…….model.odc could not be accessed.

    Do you know why this might be? Thanks.

  26. Excellent post and by far the best DAX Studio 101 tutorial I have seen – thank you very much. I have worked with quite a few link back tables using EVALUATE, but was curious as to whether it is possible to generate a pure table version of a cross tab/flattened pivot type output using a DAX query?

    I haven’t been able to figure out way to dynamically generate the values of a field from your data model table to be independent fields in a DAX query (so you could have a table that could have a new column added for user response). Could you have a table from an EVALUATE statement use your CalendarYear values as the column headers an actual table version of what would otherwise be a flattened pivot table (or is MDX or a pivot table really the only simple way to output data in that format)?

    1. So are you saying you want to be able to load the results of a DAX query in Excel, add some data in a new column and then load the new column back into power pivot? I know Imke has such a solution, but I haven’t had the time to look at it.

      1. Yes, I am trying to leave a new column that can accept feedback in a drop down for multiple users and then just load the column back. I can check on Imke’s site, thank you.

  27. Matt…
    Fantastic effort demystifying DAX Studio and now forcing me to request an approval from my IT security team.

    One question I have is can the DAX query pull parameters from the excel workbook similar to what PowerQuery / M can do?
    Regards,
    Mike

    1. DAX Studio on,y connects to the data model. So if the parameters are loaded in Power Query but not Power Pivot, then you can’t access them. The easy solution is to simply add the parameter table to the data model too. You can hide it from client tools if you want, but it will still be visible to DAX Studio.

  28. Thank you very much for a very informative explanation of what DAX Studio can do. I am a relative beginner with Power Pivot and I was going through the install process. I discovered that the System Requirements include (1) Microsoft AMO library, (2) Microsoft AMOMD Library and (3) .NET Framework 4.5 + VSTO. I am not familiar with these. Am I able to install DAX Studio on a standalone PC? I apologize if this is not the correct forum for such a query.

    Ken

  29. Great summary Matt.

    I’ve learned something with the last section: “Use your Power BI Desktop Model as a SSAS Server”.

    It would be interesting to see how far this option could be pushed (maybe with VBA) to make a true Core/Thin workbook solution.
    I’m planning raise this issue eventually on your forum to see what’s other are doing.

    Eric

    1. There are technical considerations, but probably also licensing considerations. It is likely that you are not licensed to use Power BI Desktop as a server (just a guess). So while it works, and you can do it, if you rolled out a BI strategy using this approach there may be some problems – not sure.

      1. @ Matt

        On the Power BI User voice forum the Idea – Ability to connect Excel to Power BI Data Model and create Pivot/Charts – Has been marked as STARTED – by MS – So this will eventually happen in the near future

        ttps://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8984308-ability-to-connect-excel-to-power-bi-data-model-an

  30. Thanks for writing the user’s manual for DAX Studio. You have some awesome content in here that I have not seen anywhere else.

    For documenting measures, I use the following query so I don’t need to clean things up in Excel:
    select MEASUREGROUP_NAME, MEASURE_NAME, EXPRESSION
      from $SYSTEM.MDSCHEMA_MEASURES
      where MEASURE_AGGREGATOR = 0
      order by MEASUREGROUP_NAME

    1. Hi Mike, thanks for posting this SQL Script. I am constantly amazed at how much Excel users can learn from SQL professionals, and how tidbits like this can add value.

      One of the reasons I like blogging is so I can find the information I need later. Now I know where to come to get this SQL script 🙂

      1. I store it in OneNote, where I added a link to this article as the best DAX Studio tutorial I’ve found. I saw it thanks to a tweet recommendation from Marco. When Marco calls it amazing, you know it’s good.

        You should add the SQL to your post to make it easier to find. 😉

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top