Complete Clent-side PowerPivot Solution in Excel 2013
This link handles a complex PowerPivot DAX extremely well.
This is the 1st of my favorite Excel/PowerPivot links that I am posting here.
For posterity, here is a list of links to articles/files I have published at various Internet sites. Be aware that these links may not be permanent.
Archive of Excel Experts E-letter:
http://www.j-walk.com/ss/excel/eee/index.htm ‘closed down – link no longer good
List of my PowerPivot articles at:
See my entry at Steve Bullen’s Excel MVP web page:
Download my Excel games at:
“New” articles at Daily Dose of Excel
In Excel, worksheet modules can contain sub procedures that will run just like any other VBA procedure. The same is not true for function procedures. If you add the following function to the sheet module of Sheet1 and attempt to use it as a function in Sheet1, it will not be recognized (#NAME error).
Function Mult(a, b)
Mult = a * b
Indeed, it will not even show up in Excel’s intellisense when you try to type the function name in a cell.
However, the sheet level function can be recognized by a sub or function procedure that resides in a general module. So, for example, if the function shown above is placed in the sheet modules for Sheet1, Sheet2 and Sheet4 and cells A1 & A2 on those sheets contained values of (1,2), (3,4) and (5,6) respectively, the following function in a general module
Function MultResult(m, n)
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
MultResult = MultResult + wks.Mult(wks.Range(m), wks.Range(n))
will return the value 44 when entered in a cell as =MultResult(“A1”,”A2”).
A potential advantage can be gained by using this methodology since the sheets containing the functions in their sheet modules do not have to be in any particular position in the workbook for this technique to work. This is just a basic example of what can be done to take advantage of this Excel “quirk” in a productive way.
The following DAX measure will return the week-to-date total from a PowerPivot table containing only a Date and SUM column. No calendar table or other calculated fields are needed with this measure.