Category Archives: PowerPivot DAX

#Excel Super Links #152


Bulk Download of Power BI Custom Visual Sample Files


Highlight Cells Referenced in Excel Formulas

Power Query: In Your Face(book)

Get Latest Storm Information in #Excel Using Only An Address and Storm Name



#Excel Super Links #150 – Special Edition


To celebrate the 150th issue of Excel Super Links, here are the links to all of my content at Although there are no descriptions, they are fairly self-documenting.




#Excel Super Links #148 – Shared by David Hager


Using Custom Lists in Excel

Power Query: a Function Function

Excel Pivot Chart Drill Down Buttons

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

Advanced Filter Magic


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



In this article,

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

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

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:

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), _



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.


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

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.

#Excel Super Links #147 – Shared by David Hager


Introducing the Timeline Storyteller custom visual for Microsoft Power BI


Filter data based on a comma separated list using CALCULATE and PATHCONTAINS

Why I STILL Teach DAX in Excel: My Lament to Microsoft

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel


#Excel Super Links #146 – shared by David Hager


How To Solve a Complex DAX Problem

Dax Gamechangers – Some useful features and functions from early projects

Power BI Drill Through
Calling Power BI API using Power BI Desktop to document Power BI Service

#Excel Short and Sweet Tip #19 (Invaluable Excel Speller