Adding Multiple DAX Measures to Non-PowerPivot Versions of #Excel using an User-Defined VBA Function

 

 

In this article,

http://dailydoseofexcel.com/archives/2017/07/10/look-ma-no-powerpivot/

Jeff Weir pointed to a video made by Mike Girvin about adding measures to non-PowerPivot versions of Excel (link below)

https://www.youtube.com/watch?v=FVVK-8QZC1M&t=422s

Mike demonstrated how measures can be added to a data model in these “disabled” version through pivot table options. Please view this video to see how Mike did it.

The link to the working file for this video will be referred to in this article (Thanks, Mike!).

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1269Finished.xlsx

You can download this file and reproduce the technique presented here.

Although it is not well-known, Microsoft started at Excel version 2016 (Office 365) marketing versions that do not have PowerPivot capability. For details on this, see:

https://blogs.office.com/en-us/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

So, this article is dedicated to those who purchased non-PowerPivot versions of Excel 2016, although the technique presented here will work on any version of Excel 2013 or greater.

Jeff Weir mentioned in his article that since some Excel 2016 versions did not have the full-blown PowerPivot capability, and that VBA could be used to build a user interface to the data model. Well, I have not created a UI, but I have made a way to add multiple measures to the data model using an user-defined function. The code for the VBA function is shown below. To use this, add astandard module in the VBE and then save the workbook as .xlsm. Then, copy/paste the code into the module.

 

Function AddMeasure(TableName As String, MeasureName As Range)

Application.Volatile False

With ActiveWorkbook.Model

For Each mCell In MeasureName

mFormat = mCell.Offset(0, 2).Value

.ModelMeasures.Add mCell.Value, .ModelTables(TableName), mCell.Offset(0, 1).Value, _

Switch(mFormat = “Boolean”, .ModelFormatBoolean, mFormat = “Currency”, .ModelFormatCurrency, _

mFormat = “Date”, .ModelFormatDate, mFormat = “DecimalNumber”, .ModelFormatDecimalNumber, _

mFormat = “General”, .ModelFormatGeneral, mFormat = “PercentageNumber”, .ModelFormatPercentageNumber, _

mFormat = “ScientificNumber”, .ModelFormatScientificNumber, mFormat = “WholeNumber”, .ModelFormatWholeNumber), _

mCell.Value

Next

End With

AddMeasure = “DONE”

End Function

Then, place the following information in the range D10:F14.

 

NetRevenue	SUMX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	DecimalNumber
MaxRevenue	MAXX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	PercentageNumber
MinRevenue	MINX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	Currency
AverageRevenue	AVERAGEX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	General
CountOfRevenue	COUNTAX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	General

 

To run this as a worksheet formula, type this formula in any cell.

=AddMeasure(“fTransactions”,D10:D14)

This will add the 5 measures to the data model, as shown in the Pivot Table Fields list.

After the 5 measures are added to the pivot table, the resulting pivot table will look like this.

Of course, the DAX formulas to be added have to return valid results, or the procedure will fail.

This powerful technique is yet another reason why users should not completely abandon Excel for Power BI desktop, as discussed in this article at powerpivotpro.com

https://powerpivotpro.com/2017/09/excel-is-still-the-best-tool-for-teaching-dax/

And, this technique does not HAVE to be run from a UDF, but I am still amazed that it can. I am sure that you will find this very useful.