skip to Main Content


Force 10 From Navarone

“Excuse me, sir, you don’t need 20 men, you need Miller Banfield.
He’s an expert with explosives Excel, sir. He’s probably the best in England.”

 

 

That’s right folks, after prowling the comment threads for months now as one of the most insightful contributors, Colin Banfield of BIExcel.com is taking the plunge and sharing a full guest post with us.  This is indeed a very positive development for us all 🙂

Today, Colin is giving us more info on using Notepad++ to write PowerPivot formulas.  Take it away Colin 🙂

Using Notepad++ as an editor for creating, reading, and troubleshooting DAX formulas

Rob recently wrote an article about the difficulty he experiences editing complex formulas in PowerPivot, even though he helped to create editor used in the Measure Settings dialog box. You can read Rob’s article here https://powerpivotpro.com/2010/03/27/two-observations/. In the article, Rob noted the following as shortcomings of PowerPivot’s built-in editor:

· Can’t indent formulas
· Can’t save formulas
· No find and replace option
· Can’t change the font size

To address these issues, Rob has been using Notepad as his PowerPivot formula editor. In the above-mentioned article, Rob solicited opinions on alternative text editors. Some folks, including me, suggested Notepad++. Notepad++ is a free text editor that might be viewed as Notepad on steroids. However, the point is not that Notepad++ has more features than Notepad – it’s that you can customize the program to work specifically with DAX formulas. The purpose of this article is to explain how the customization is done, and the many benefits you gain from the effort.

You can download the latest version of Notepad++ from here http://notepad-plus.sourceforge.net/uk/site.htm. During the installation, I strongly suggest that you don’t check the option to bypass using the %APPDATA% folder for storing Notepad++ settings (the option is unchecked by default is used mainly for portable installations). If you have already installed the program and checked the option during installation, I recommend that you completely uninstall the program (including everything under the Notepad++ installation folder) and start over.

Figure 1 shows the Notepad++ main window. The file manager window on the left is a plug-in that you can install through the Plugins menu. The plug-in is called Explorer. The built-in file manager is useful when you want to quickly retrieve saved formula files.

clip_image002

Figure 1 – Notepad++ main window

The first step in customizing Notepad++ is to create a custom language for DAX. The purpose of a custom language is to provide special syntax highlighting for various elements of the language. Choose ViewàUser -Defined Dialogue…The User-Dialog box appears as shown in figure 2.

clip_image004

Figure 2 – User-Defined dialog box

Click Save As… and enter “DAX” (without the quotes) in the Name text box. After you click OK, you will notice that extension (Ext) box appears at the top right of the User-Defined dialog box. Type an extension in this box (txt, dax, foobar, whatever), and save your formula files with this extension. This ensures that when you subsequently open the file, Notepad++ applies the custom language for editing the file. You can associate more than one extension with a custom language by separating the extension names with a space in the Ext box.

After you enter an extension, you can explore the various tabs in the dialog box. My personal setup is as follows (it has changed over time):

· Folder and Default tab – As shown in figure 3.

clip_image006

Figure 3 – Folder& Default tab

The folder open and close keywords are used expand/collapse a block of code. This might be useful for delineating parts of a DAX formula that can be used as intermediate measures in a PivotTable (see figure 7).

· Keyword Lists tab – See figure 4.

clip_image008

Figure 4 – Keyword Lists tab

In the “1st Group” list box, I pasted an alphabetized list of DAX functions.

· Comment and Number tab – See figure 5.

clip_image010

Figure 5 – Comment & Number tab

· Operators tab – See figure 6.

clip_image012

Figure 6 – Operators tab

Naturally, each user will personalize these dialogs to suit his or her taste. Notepad++ saves the configuration after you exit the dialog box. To use your custom language, you must choose it from the Language menu. Custom languages appear in a separate section at the bottom of the menu. Currently (version 5.6.8 at the time of this writing), there is no way to make your custom language the default language. You must select the custom language every time you open Notepad++ and every time you open a new file. This is a very annoying and stupid limitation. If you don’t assign an associated file extension for the custom language, the syntax highlighting for the default built-in language will be applied after you open the file. However, there are many user requests to make a custom language the default language, so it’s possible that the feature will appear in a future version of the product (hopefully, very soon).

Figure 7 shows an example of using your new DAX language to edit a DAX formula. Notice the vast improvements in readability over editing the formula in the PowerPivot Measure Settings dialog box, or in Notepad. The bottom left of the windows displays the language in use.

clip_image014

Figure 7 – Example DAX formula with syntax highlighting, bookmarks (blue blobs) & folding blocks

Pretty cool, no? However, we’re just getting started. The next step in customizing the language is adding function auto-complete, function parameter tooltips and function description tooltips. You heard that right. It takes some time to create the text for the auto-complete XML file, which must have the same name as the language (is this case the name of the auto-complete file would be DAX.xml). You must place DAX.xml in the Notepad++pluginsAPIs folder, and the turn on the appropriate Auto-Completion options in the Preferences dialog box. These options are shown in figure 8.

clip_image016

Figure 8 – Auto-Completion options

I’ve created a DAX.xml file, and anyone interested in using it can download it here:
http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx

The next couple of figures illustrate how function auto-complete and tooltips work.

clip_image018

Figure 9 – Function auto-complete

clip_image020

Figure 10 – Function parameter and description tooltip

The only functionality you lose from PowerPivot’s built-in editor is the very useful table and field names auto-complete. You can use the PowerPivot editor to auto-complete table and field names that you paste into Notepad++. In many cases, like that shown in figure 7, you end up reusing the same fields in different parts of the formula – which is then a simple cut and paste operation.

I encourage you to explore the many other features of Notepad++ not covered in this article. Other features I find useful include:

· FileàLoad Session & File Save Session. Because Notepad++ uses a multiple document interface (each document appears in a separate tab), you can save all the formula files associated with a project as a session.

· Bookmarks. You can toggle a bookmark on and off in several ways, for example: 1) click the right-side of the line number in the margin, 2) select a line and enter Ctrl+F2. You can also bookmark all lines containing specific text. Bookmarks are useful if you want to include folding keywords (as shown in figure 7), or comments inside the DAX formula. After you bookmark the folding keyword lines and comment lines, you can delete these lines by choosing SearchàDelete Bookmarked Lines. After you delete the bookmarked lines, you can paste the formula into PowerPivot.

As a final note, I’d like to say that Notepad++ has helped tremendously in managing my calculated measures. With complex formulas, I tend to create many intermediate formulas so that I can see the calculated results in the PivotTable. I don’t want to expose these intermediate calculations to the end user, so before deploying the PowerPivot model, I have to amalgamate the intermediate formulas into a single formula. It’s very unfortunate that PowerPivot doesn’t allow you to hide calculated measures.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 29 Comments
  1. 1. Perhaps we can give some sort of sacrifice to the PowerPivot gods that they may bestow upon us a full official DAX.xml file so users (such as Mr. Banfield, a fine Canadian upstart.. but I digress) don’t have to do this work themselves.
    2. Nice posting!
    3. I’m not sure that my measures will ever get complex enough to need a decent editor (oh how I hope this would hold true), but please add me to this list: john.constant at gmail.com
    4. Don’t suppose there’s a simple notepad++.ini file with these presets already in it to share/customize? I’m not saying I’m lazy, I just don’t see a need to improve on established procedures.

  2. John, are we acquainted? If so, you have the advantage 🙂

    User Defined customizations are kept in the userDefineLang.xml file, which is located in the C:Users\AppDataRoamingNotepad++ folder. I’ve had issues with customizations not updating after either modifying or replacing the file, but I need to do some further tests. I’ve provided a text file at the appointed link that contains an alphabetized list of DAX functions for pasting into the User Defined dialog (figure 4). It takes only a few minutes to complete the choices in the various User Defined dialog tabs.

  3. wow, thank you, thank you!

    appreciate you pros letting the rest of us ride your coattails…although educating the great unwashed excel masses can’t hurt powerpivot adoption.

  4. That looks interesting even though I don’t have access to PowerPivot (yet?).

    Do you have a similar setup in Notepad++ for regular Excel formulas? That would come in handy when writing those ‘mega formulas’ 🙂

  5. @Greg, you’re welcome. 🙂

    @m-b,

    The idea did occur to me. There are a lot more functions in Excel though, so it’ll take some time to build an “Excel.xml” file. It’s something that I’ll probably develop over time.

  6. @Colin: thanks for the reply. Maybe that’s an idea for an Excel add-in? It would be nice to have a button on the Ribbon allowing you to edit the formula in a similar way as shown above. Shame I don’t have the brain power to build it 🙂

  7. Hello!

    Thanks for this very interesting idea/post – and thanks for posting a link to dax.xml.
    Could you also post a link to a list of dax-keywords – so I don’t have to enter them manually?

  8. Hi Markus, sorry for the late response – I didn’t receive notification of your post.

    There is a text file named DAX Functions in the same location as the DAX.xml file.

  9. Hi,

    I have a related question about editors and adding DAX formulas. Say I have a PowerPivot workbook in which I have identified 7 columns to which I want to apply time intelligence functions and calculate %Change, lagged measures and the like. There are a number of date intervals that are relevant; 1 year ago, yesterday, 7 days ago, 14 days ago, 28 days ago and so on. In total, and I don’t think this scenareio is particularly unusual, I want to apply 5 different time intellegence functions x 9 different date intervals x 7 = 315 calculated measures. It is easy enough to use Excel text concatenation functions to construct all 315 measures in one go by using a “template” formula such as [COLUMN]( DATEADD(DimDate[DateKey], Periods, Time)) and then using nested “SUBSITITE” functions to create the actual DAX statements.

    It is however very tedious to add the 315 DAX formulas one by one – is there a way to “bulk add” DAX measures?

    1. OK, first of all, this is my favorite comment ever that at first seemed like spam. I get so many spam comments that are basically “hey look at my website” and seranataflowers seemed to fit the bill.

      But no! This is not only a legitimate question, it is, in fact, a question of utmost proportions! 🙂

      Peter, quite simply, you and I are birds of a feather. This has been killing me too. And sadly, no, there is no bulk add mechanism. I’m working on a trick or two that *might* pull this off however, and will let you know if it works out.

      -rob

  10. Note that if you are an inveterate meddler like myself you will undoubtedly dock that User-Defined Dialogue at some point. This is fine if you have Windows XP, but if you use Windows 7 then it stays docked, will not undock, and you can’t do anything in that dialogue thereafter.

    The only way out I could find was to edit the config.xml file to tell Notepad++ that the dialogue was undocked.

  11. […] dat ook gelukt is… Ik heb in ieder geval één hele zinvolle tip uit zijn sessie gehaald: het gebruik van Notepad++ met de DAX language template van Colin Banfield. Chris sloot zijn verhaal af met enkele conclusies over […]

  12. Is it possible to determine what font is used in PowerPivot Measure Settings window, so we can match the look in Notepad++ ?

  13. Has the format of these XML files changed since these posts. The Language definition screens look a bunch different, and the import did not seem to work.

    1. I was able to download Colin’s dax.xml file and paste it into the APIs folder, but I got somewhat lost translating the earlier version instructions into the current version of Notepad++. I would download the earlier version used in the instructions, but I don’t know which version to download.

Leave a Comment or Question