One of the, not terribly well known, but very useful, advantages of a Power Pivot pivot table is the ability to convert a dynamic pivot table to a static set of CUBE formulas. This facility is to be found under the pivot table Options tab's OLAP Tools -> Convert to Formulas.

Not only is this useful for freeing the layout of reports from the rigidity of a pivot table but also allows for the construction of Power Pivot sourced reports and/or test suites that can interface with Excel proper.

If you look at the generated CUBE formulas you’ll see snippets of MDX , not DAX, used in the various functions. This can make writing your own variations on these functions a bit off-putting if you’ve never seen MDX before, but don’t be, the level of MDX knowledge to use CUBE functions (or indeed rolling-your-own sets to be found under the same Pivot Table Options tab) is not that difficult to master.

The DAXCUBEMEMBERFILTER() function knows enough MDX to convert the filters within CUBEMEMBER() (and the more difficult to parse, CUBESET()) formulas to DAX snippets for passing as filter arguments to DAX() function calls (either as cell formula calls or from within a VBA UDF).



...where cell points at either a CUBEMEMBER() or CUBESET() formula.

The return value will be a DAX snippet, for example:


…or from a CUBESET() formula:

FILTER(VALUES('Invoices'[Country]), 'Invoices'[Country]="DE"||'Invoices'[Country]="ES")