How to Document DAX Measures in Excel - Excelerator BI

How to Document DAX Measures in Excel

Level: Beginners

I often get asked if there is an easy way to create documentation for DAX measures when using Power Pivot for Excel.  I am not a big fan of documentation for the sake of it, but I do see value in having “some” appropriate level of documentation.  I think a good balance of the right amount of documentation vs the effort to write and maintain the documentation is to use the Power Pivot UI itself.  I explain how I do it below.

Writing DAX Measures From Within Excel

I always teach students at my Power BI training courses to write their measures from within Excel via the “new measures” dialog box (and specifically not to write them from within the Power Pivot window).  The process is always as shown below.

  1. Set up a pivot table that has some relevance to the measure you are writing, then click in the pivot table (shown as 1 below)
  2. Select Power Pivot Menu \ Measures* \ New Measure* as shown below (*called Calculated Field in Excel 2013)

image

The reasons I teach students to do it this way are simple.

  1. You get a dialog box/wizard that shows you every step that is required to complete the task.  Just complete each step one at a time and you can’t go wrong.
  2. You get to check the measure for errors before completing it (shown as 2 below).
  3. Once you add the measure to your pivot table (shown as 3), you get to see immediately if it is working as you expected.

image

image

And there is a fourth reason to use the wizard that is less obvious.  The Description box (shown as 4 above) is a great place where you can make notes about your DAX formulas (aka documentation).    In Power BI Desktop you can add comments into your DAX expressions, but this is not possible in Excel, so the description box is the best option you have.

Create Your Documentation

If you diligently add comments into the Description box above, you will then be able to extract this information into a documentation page using the Power Pivot Utilities Toolbar.  I have previously reviewed this great little tool here, so you can go back and read about it if you missed it.

After installing both tools, you will then see the PP Utilities Toolbar in Excel (shown as 1 below)

image

Click on List Measures (shown as 2 above).  After a few seconds, you will see a new page in your Workbook called “Measures” like shown below.  Note the “Description” column on the right hand side. This column is sourced directly from the information entered into the Description box shown further up the page.

image

What’s more, if you select the measures on your page (Shown as 1 below) and then click Format DAX Expressions (2 below), the measures will be nicely formatted so they are easy to read.

image

This page is then your documentation and you can refer to it at any time you need to see the big picture.

Always Up to Date

One important feature of this type of documentation is that it is very easy to maintain.  If you make changes to any of the measures or comments in your data model, you can simply re-run the extract process so you have an up-to-date list of all the measures.  You can also do the same for Calculated Columns and also relationships.

What About Excel 2010?

Unfortunately the Power Pivot Utilities Addin only works for Excel 2013+  But all is not lost.  It is possible to extract the Measures from your Data Model manually using DAX Studio.  You can read my blog post about how to do that (and other cool things) with DAX Studio here

7 thoughts on “How to Document DAX Measures in Excel”

  1. Hi Matt, I’m new at the BI/Powerpivot/Power Query (read Robs book, trying to do yours lessons/ and reading Ken Puls book to) and my archive gets corrupted (happened more than once), is there a way “easy” to import the measures from this documentation back to PowerPivot? I was trying to get my PowerQuery codes to a new file, to get my data tables. So the second step would be put every single measure back. But I can’t copy/paste a measure direct into powerpivot table (measure:=expression) because excel crashes when I press enter. (I use excel 2016 x64 – and updates are kind of difficult with IT, I can’t know if I’m missing some update). So my question: Is there a way to put those measures back to powerpivot, besides one at the time? thx Daniel

  2. Hi Matt, excuse me but in power bi i can’t find how to add any comment, there’s no option (i think so), and another important thing, instead of power pivot, i can’t change the name of the same measure in differents visuals, i’m forced to created a new measure as this [New name for the visual1] = [name of the measure].
    I hope they’ll add this new options in future versions of power bi
    Best regards from Barcelona!

Leave a Comment

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

Scroll to Top