DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in

I mentioned in my last post that it was possible to create DAX measures (and indeed Calculated Columns, KPIs etc.) without using the PowerPivot add-in but instead using AMO to do so. The facility has always existed in HAMMER but only as a hidden option that I often used to iterate quickly through a set of measure variations (or load standard sets) during model development; but I wasn’t 100% convinced it was worth the effort to make it more production quality in order to add it to HAMMER’s public interface. But, with the extraordinary removal of PowerPivot add-in as an option for all retail versions and all O365 SME versions of Office 2013, I decided to revisit the code and make it available as a new HAMMER command “PPMAKEMEASURES”.

This zip file contains an example of an Excel 2013 workbook (drawing data from a Northwind oData public feed) with DAX measures created by HAMMER rather than via the PP add-in.

The HAMMER formula and associated “list-of-measures” table can be found within.To use the formula on this, or another workbook, you need to activate HAMMER.xll (or HAMMER-64.xll, if using Excel 64bit) by either opening like a normal workbook (will not be installed as a permanent add-in) or put it in your add-ins folder and register as per a normal XLL (you can also use VBA’s registerDll functionality for automatic only-this-instance registering).

The first time you apply this method of DAX measure creation to a workbook, you should save, close & re-open the workbook; otherwise, you run the risk of the created measures being overwritten if you subsequently create an implicit measure (by moving a field to the Values section of a pivot table). If saved, this will no longer be a problem. If you use this in Excel 2010 you’ll always need to save,close & reopen in order to see the new measures. (Also, in 2010 you’ll need to precede any PP commands with the PPCONNECTION command e.g. =HAMMER(“PPCONNECTION”,Table2[#All],”PPMAKEMEASURES”) UPDATE: It appears you also need the PPCONNECTION in 2013 although I don’t appear to need it in my O365 version!).

To get a list of DAX Measures within a workbook use the PPLISTMEASURES command (e.g. =HAMMERToRange(“PPCONNECTION”,”PPLISTMEASURES,”Sheet2!A1″) will paste a table to Sheet2 starting at cell A1). The table will be in the format required by the PPMAKEMEASURES command and is intended as a quick way of transferring measures from one workbook (even if 2010) to another. (Note: this will list both implicit and explicit measures, best avoid creating implicit measures using this method!)

The table is position based (i.e. heading values not important, just as long as there is a heading line); columns represent – Table Name, Measure Name, DAX, Format and Visible. If the 3rd column, i.e. the DAX expression, is blank, the measure will be deleted, otherwise, any existing measures will be deleted and replaced with the new definitions.

This has only been tested against  O365 versiond of Excel 2013, retail versions may be different. I needed to hard-code the location of the AMO & ADOMD dlls (unlike 2010 they’re no longer in GAC and are not on a PATH – well, likely they are, but only in the virtual file system now used by Office).

The location for the AMO library for 32bit O365 Home Premium is  “C:/Program Files/Microsoft Office 15/root/vfs/ProgramFilesCommonX86/Microsoft Shared/OFFICE15/DataModel/Microsoft.Excel.AMO.dll” let me know if your setup is different. (UPDATE: As Marco discovered, enterprise and most likely retail versions do not use VFS, I‘ll post a fix to this tomorrow). UPDATE: PATH issues with none O365 versions should now be fixed.

If you’re a SME or sole-trader and you intend to use PowerPivot extensively you really need to purchase (as I have) the O365 Professional Pro subscription for your “datasmiths”. The rest of your crew can use Retail or O365 offerings (as they can still “consume” DAX powered models). But if, for some reason, you can’t or won’t purchase ProPlus, or you just want to take advantage of the fast build-redesign-build cycle offered by PPMAKEMEASURES, then hopefully, this will be of use.

Oh, and Happy St. Patrick’s day!

Advertisements

18 responses to “DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in

  1. My 64 bit Excel has the DLL here: C:\Program Files\Common Files\microsoft shared\OFFICE15\DataModel – how can I fix this path?

    • Hi Marco,

      I’m guessing you have an enterprise non-O365 or Retail version?

      I’ll change the add-in tomorrow to reflect this and will re-publish (obviously retail & ent. versions don’t use the new vfs). I’ll eventually change add-in to allow dlls to be picked up from install folder and/or specify location but didn’t do this time to get the type of feedback you’ve so kindly provided 🙂

      Tom

      • Yes, I have an Enterprise license (non Office 365).

      • Hi Marco,

        I’ve uploaded a fix. When you can find the time give it a try.

        It looks like I can see the VFS “view” of the world so will not need to reference the physical folders directly just use the PATH either virtual (as in O365) or actual for retail & ent. versions.

        Tom

  2. It appears to me that if you can do this, you might also be able to create CUBE-like worksheet functions that operate via HAMMER and DAX.

    • Yes.
      Try:
      =HAMMER(“PPCONNECTION”,” ..any DAX query.. “,”ADO”,”TABLETOSCALAR”) to return the first row, first column of the resulting table (leave out the TABLETOSCALAR and use HAMMERToFit or HAMMMERToSheet or HAMMERToRange(,,,”aRangeInStringFormat”) to fetch the whole table).

  3. Pingback: An alternative model for faster Excel Self Service BI–Excel Data Insights Addon– Part I | Rui Quintino Blog

  4. Ok now it works – you just have to remember to open the PowerPivot addin after opening the workbook because if the connection has not been initialized, you get another error (probably because the connection cannot be found).
    Thanks!

    • That’s interesting as I don’t need to do that with O365 version?

      In 2010, it is necessary to have a PP based PivotTable AND to ensure the workbook has a PP connection either by opening PP window or doing a refresh. To get around that, I have the PPCONNECTION command to precede any PP actions in HAMMER.

      e.g. =HAMMER(“PPCONNECTION”,Table2[#All],”PPMAKEMEASURES”)

      It both wakes up the connection and also passes in an ADO connection for use with the ADO command allowing DAX & DMV queries. You can still use the PPCONECTION with Excel 2013, and it appears in some situations it’s also required; it will remove the necessity of opening a PP window to initiate the connection.

      Tom

      • Had you 1st enabled connections via the “security” Enable Connections request that appears if a workbook is downloaded from the internet?

      • No I saved the workbooks on a folder, I open datasmith-64 first, then I open Example_Create_Measures_no_Addin from the same Excel instance and if I edit and press enter on the formula, I get the following error:

        “ABEND – Error executing code: System.MissingMemberException: ‘str’ object has no attribute ‘Databases’
        at IronPython.Runtime.Binding.PythonGetMemberBinder.FastErrorGet`1.GetError(CallSite site, TSelfType target, CodeContext context)
        at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
        at Microsoft.Scripting.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
        at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
        at Microsoft.Scripting.Interpreter.LightLambda.Run7[T0,T1,T2,T3,T4,T5,T6,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5, T6 arg6)
        at IronPython.Compiler.PythonCallTargets.OriginalCallTarget6(PythonFunction function, Object arg0, Object arg1, Object arg2, Object arg3, Object arg4, Object arg5)
        at IronPython.Runtime.PythonFunction.FunctionCaller`6.Call6(CallSite site, CodeContext context, Object func, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5)
        at System.Dynamic.UpdateDelegates.UpdateAndExecute8[T0,T1,T2,T3,T4,T5,T6,T7,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5, T6 arg6, T7 arg7)
        at Microsoft.Scripting.Interpreter.DynamicInstruction`9.Run(InterpretedFrame frame)
        at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
        at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)
        at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)
        at IronPython.Compiler.RuntimeScriptCode.InvokeTarget(Scope scope)
        at python.PyEngine.evaluate(String code, Object xliteDB)”

    • I’ll set up a new clean virtual machine tomorrow and retry, as I’m not seeing this on my current setup. In any case putting command “PPCONNECTION” as 1st HAMMER command should get around problem.

      Again thanks for the feedback, much appreciated.

      Tom

    • Set up a clean machine installed O365 and I now see that same problem. Difference I can see is the “bad internet file” warning doesn’t appear on the PC I developed on even when I re-download from the internet, I guess it recognizes the file’s hash signature?

      So it looks like PPCONNECTION required for both 2010 and 2013 to ensure model’s awake. Have changed example to reflect this.

  5. Pingback: Look’s like Star Schema is back on the menu! | Gobán Saor

  6. Pingback: The Model of a very modern PowerPivot – without the add-in | Gobán Saor

  7. chris (from France)

    Hello

    Thanks for this product : I am so disapointed by 2013 without full PP.

    I am in France and use Office 2013 in French and France for Regional parameters in Windows.

    I succeed configuring the table of measures.
    The formula =HAMMER(“PPCONNECTION”;”Table1[#Tout]”;”XLRANGE”;”PPMAKEMEASURES”) is working well.
    The formula =hammerToRange(“PPCONNECTION”;”PPLISTMEASURES;”Test!A1″) result is “OK” but in the Test!A1 cell, I get
    ABEND – ExcelError

    If I configure Windows in US regional parameters I have no error but I get nothing.

    Have you any idea ? Thanks