OK, last week I showed you a **parameterized PowerPivot report** that contained no pivots anywhere. How did I build it?

### Best Tutorial: Start with a Pivot

The easiest way to show you is to start with a PivotTable, even though once you’ve mastered this technique, you won’t always want to start this way.

Here I’ve got 4 measures from the Plays table broken out by ‘Clean Players’.[FullName], and the report is sorted by the [Rushing Yards] measure:

First, I want to get rid of that blank value at the top – over the years, an awful lot of “nobodies” amassed a lot of stats, and apparently STATS did not care enough to capture their names. Accordingly, I don’t care enough to want them in my report.

So, I’ll just filter them out. Easy enough, as shown here at right.

But I also want to limit my report to the top 20 players. How do I do that, now that I have already applied a filter to this field?

When I try to add a Top 10 filter to this field, it clears my filter that removed the blank, and I get that blank, unknown player back in the report.

So how do I do this? Well, I use a feature of PivotTables that Allan Folting argued for, and I argued against, back in 2006. He was right, I was wrong, and the feature has helped me numerous times since 🙂

That feature is hidden under the Options button on the PivotTable Options ribbon tab. It defaults to off. Once enabled, I can go back to the Pivot and add a Top 10 filter, set to show the top 20 players as sorted by Rushing Yards:

Checking that checkbox allows me to then add my Top 20 filter on top of the original:

Then I add some slicers, which yields the basic starting point that I want:

### Enough with the pivot, Rob! We want the pivot-LESS report!

Indeed. Time to blow this pivot into a hundred pieces. Literally.

On the Pivot Options tab of the ribbon, under a dropdown called OLAP Tools, there is a button named “Convert to Formulas.” On normal pivots, this whole dropdown is disabled… because normal pivots are not OLAP pivots.

But PowerPivot-backed pivots are OLAP, because under the hood, the PowerPivot db engine is an OLAP engine. So this button is enabled:

Watch what happens when you click that button:

[youtube=http://www.youtube.com/watch?v=M1U_pPawalY&hd=1&w=640&h=480]### Recap

Briefly, let’s recap what we see in the video:

- Every cell in the pivot has been converted to a formula
- Each formula returns the same result that the cell previously contained (when it was part of the pivot)
- Slicers still work – the formulas return different numbers reflecting slicer selections
- The formulas can be treated just like regular spreadsheet cells – moved, rearranged, deleted, etc.
- There are two functions involved – CUBEMEMBER() and CUBEVALUE()
- Generically, we call this feature “Cube Formulas”

### Cube Formulas are NOT DAX!

I know that cube formulas are new to most of you, and seeing how I am introducing them in the context of PowerPivot, it’s natural to suspect that these formulas are part of DAX.

But they are not. They are built-in to regular Excel and are there before you install PowerPivot. Remember, DAX can only be used on tables in the PowerPivot window, and in the New Measure dialog. DAX cannot be used in normal Excel spreadsheet cells.

Cube formulas are just another way for Excel to interact with certain kinds of data sources. And PowerPivot is one data source that qualifies. That’s good news for Excel pros – a brand-new way to build reports. It’s a free benefit of PowerPivot 🙂

### CUBEMEMBER() and CUBEVALUE()

As shown in the video, let’s inspect the formulas in three specific cells to show you what’s going on here.

**First, the Edgerrin James label cell:**

Brushing over syntax for a moment, that formula says “this cell now represents Edgerrin James” – the string “Edgerrin James” was returned from the PowerPivot database in fact.

**Then the “Rushing Yards” header cell:**

Again brushing over syntax, that formula means “this cell now represents the Rushing Yards measure.”

**And lastly, the 9842 value, which is Edgerrin James’ total Rushing Yards:**

I put this one in edit mode to show you that it references the Edgerrin James and Rushing Yards cells. So this CUBEVALUE formula shows the intersection of Edgerrin James, and Rushing Yards, just like it did in the PivotTable.

Notice how it also references the two Slicers. That’s how the formula returns different values as the slicers are manipulated. Again, just like it did in the PivotTable.

### More to come

Cube formulas are indeed the core trick behind my “pivot-less” report. But there are several elements yet to be explained, like the parameterization, and even more importantly, how the sort order of the report can change in response to parameters and slicers.

I also want to show you what happens with this report when published to SharePoint.

I think this is enough for one post though 🙂

Kudos to Allan Folting for winning that argument. The Allow Multiple Filters trick is cool!

So… is that also the case for VSTO?

Can we connect to PowerPivot data programmatically? Should we do it like connecting to OLAP?

Thanks!

Welcome 🙂

Can you give me an idea of the scenario you have in mind? Yes, querying the data programmatically is possible (and it is OLAP), but I don’t want to mislead you, so it’s best if I answer more precisely.

-rob

Thanks!

Data in powerpivot is flattened.

So if I access the OLAP cube directly, can I access dimensions? Hierarchies? Can I get a unique set of values per column by accessing a level in a dimension… that pretty much where I’m heading.

Yinon

OK, one more thing I forgot..

Performance wise, will it still have the benefits of powerpivot (the sub-second filtering and sorting) or would it be like regular mass data functions (getting performance hit after 1K+ rows calculations)

Thanks!

Yes, you can access the data as an OLAP cube, even though the tables in the powerpivot window are in fact “flat.” Those source tables in the powerpivot window are similar to the Data Source View in traditional cubes.

Note that PowerPivot does NOT support hierarchy definition, so you will not see hierarchies in the OLAP interface.

But there is one dimension and one measure group per table in the powerpivot window. All columns in each table are exposed as attributes. And then you can define measures on top of that.

Performance at the OLAP interface is just as speedy as the powerpivot window. In many ways, PowerPivot shines more thru the OLAP interface than it does in the PowerPivot window, since the OLAP calculations are “harder.”

Lastly, interacting with a PowerPivot workbook via the OLAP interface is only supported for published workbooks (published in SharePoint). The URL is the connection string.

FAQ now updated at: http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=30

Cool…So Cube is the equivalent of the GETPIVOTDATA for an OLAP Pivot

[…] Pulling back the curtain: Intro to Cube Formulas […]

Let’s say, as a part of the Player Dimension, there was an attribute of Player called “University” that housed where each player played college ball. How would you use a CUBEMEMBER formula to retrieve “University of Miami” in the cell next to Edgerrin James?

To call any attribute related to a member you can use the cubememberproperty function

I haven’t tried this. Does it work in PowerPivot, given that member properties cannot be defined in the model?

Thanks Dany! I’m trying to call an attribute related to a cube member, but there’s nothing about this yet. I’ll start looking through the cubememberproperty function

Seems like a great tool. Can I use PivotTable functions in excel?

Hi

I am trying to build my first report using a converted PowerPivotTable (which I later change to fit my needs) with cube formulas. How do I make a selection that gives me everything but a specific value? (without using the slicer).

By the way thanks for a very good site.

Thank you Erik.

Can you expand a bit on your question? I am not precisely sure what you are trying to achieve.

-Rob

Hi Rob

Thanks for swift reply.

I am tring to make a cubevalue function that gives me the value for all “members” of a dimensions except one. As an example I am trying to sum all sales for customers but wants to exclude all instances with “blank” in the customer field. The below function gives me the sum of all sales for the blanks, but I want to reverse that.

=CUBEVALUE(“PowerPivot Data”;$B5;E$3;”[dimTime].[Year].[“&År&”]”;”[dimTime].[Month].[“&Månad&”]”;”[dimOrganisation].[Business_Unit_Desc].[“&Affärsområde&”]”;”[dimAgreement].[Customer].[]”)

I have been trying something like this but failing:

=CUBEVALUE(“PowerPivot Data”;$B5;E$3;”[dimTime].[Year].[“&År&”]”;”[dimTime].[Month].[“&Månad&”]”;”[dimOrganisation].[Business_Unit_Desc].[“&Affärsområde&”]”;NOT(“[dimAgreement].[Customer].[]”))

Best/Erik

Hi again,

I have noticed that all my files with cube functions takes forever to open up, but performs fairly well when opened. This is not the case for a file with only a PowerPivot table. Is this a common issue? Any suggestions on how to solve it?

Best/Erik

No Erik I don’t really have an answer for that one. I just generally have it filed away in my head that cube functions are slower than pivots. I have not gotten any more scientific than that.

Hi Rob,

Have you had any chance to look at this?

Best/Erik

Rob –

I have the same question as Erik, struggling to figure it out short of having two formulas and subtracting the one I do not want from the total formula. I have 5 companies and we report 4 of them together to one report user. Unfortunately they are not sequential.

Ryan and Eric –

First of all, sorry Erik for taking so long to respond. Been a very busy month.

Honestly Ryan I really like your idea of subtracting one out.

Couple other suggestions to try:

1) Define a CUBESET formula. You can reference other cells in the spreadsheet, cells that contain CUBEMEMBER formulas, to build the set. For instance, in cell D30 I have:

=CUBESET(“Sandbox”,F4:F6,”Top Three”)

Where F4:F6 all contain CUBEMEMBER formulas.

And then in cell D31 I have a CUBEVALUE formula that references D30:

=CUBEVALUE(“Sandbox”,D30,H3)

H3 contains another CUBEMEMBER formula, that returns a measure. You can assemble all kinds of crazy sets this way. If you want, you can even use Excel functions like INDIRECT, OFFSET, etc. instead of the cell reference like F4:F6

2) Use a CUBESET function and custom MDX that excludes your one undesired member. Erik’s examples above are an attempt at this. Sadly, I do not know MDX well at all, so I cannot provide the right query. But I’m sure someone reading this can.

3) Don’t forget the “.children” trick – I suspect Ryan was proposing to use that trick, combined with a second formula to subtract out the unwanted member.

Erik in your example I think this would be:

”[dimOrganisation].[Business_Unit_Desc].[“&Affärsområde&”]“;”[dimAgreement].[Customer].children“)

You’ll need to read the post that follows this original post, one that talks about CUBESET, to understand these comments better.

I have used cubeset and cuberanked member to show my sales teams by sales top/down. At the bottom of my list are the members who do not have sales for a given period. Is there a way thru nonempty (which I haven’t seen work yet) to another way to list only those sales staff who have sales in a given period.

Here is my set formula.

=CUBESET(“PowerPivot Data”,”([ladwholesalers].[reportsto].[All].[“&’Zip Codes’!$D$1&”],[ladwholesalers].[fullname].[All].children)”,’Zip Codes’!$D$1,2,”([Measures].[Production],[annuitymth1].[Year].[All].[“&$D$3&”])”)

Here is my rankedmember formula.

CUBERANKEDMEMBER(“PowerPivot Data”,$E$2,COUNTIF($B$6:B6,”Total Rank”)) where e2 is the cubeset and the countif creates a dynamic count. The overall spreadsheet uses outlines.

Thanks

An old post, I know, but the info is still great!

When I “Convert to Formulas” it does not respect the slicers. The slicers stop functioning when I do this…but I want them to work. Any ideas?

I suspect the Value cells in the pivot ARE respecting slicers. But the row/column fields are not, and that’s what giving you grief.

In other words, any cell with a CUBEVALUE function in it contains a reference to the slicer(s).

But any cell with CUBEMEMBER in it does not. You need to replace the CUBEMEMBER cells with a combination of CUBESET and CUBERANKEDMEMBER, and have the CUBESET reference the slicer.

Try http://www.powerpivotpro.com/2010/02/introducing-cubeset-and-cuberankedmember/ as a starting point, but I need to go double check something on how to make CUBESET respect slicers. There’s a trick to it.

All right, I had forgotten how hard it is to use CUBESET in that manner. Dustin, my apologies, but it’s really too difficult and tedious to be worth it, at least given my understanding of it today 🙁

Rob – thanks for looking into it. I’ll keep experimenting 🙂

Excellent blog – love it!

Dustin – I blogged about this a week or so ago http://msmvps.com/blogs/xldynamic/archive/2012/12/16/cooking-with-cubes.aspx

Rob – the cube functions you describe work great. I’m stumped on CUBEMEMBERPROPERTY though, it always returs a #N/A error when looking at an OLAP. The CUBEMEMBER isn’t useful since I need to know the value in the equation to return the value. I would like to return an attribute of the member, like the account name of the account. Do you have any posts that work through CUBEMEMBERPROPERTY syntax? Thanks!

I don’t have any posts on that function, sorry Johnny. PowerPivot doesn’t offer member props so I have not dug into them much.

Cube functions, like CubeValue, are not part of Dax. Does this mean Dax functions, specifically Calculate(), cannot be used within the CubeValue function?

Correct. Calculate and other Dax fxns cannot be used in cube formulas. But no worries! Just define a measure in Dax then “fetch” it by name via cube formulas.

Hello Rob,

I stuck with the problem with “Convert to formulas” returns null or 0 value where GETPIVOTDATA and CUBEVALUE (with all slicers included to formula) functions returns correct numbers for PowerPivot Data Model.

I have “sophisticated measure” with a lot of “IFs”. When I build pivot table all values are calculated correctly, but when I convert it to formulas SOME of cells become either blank or 0.

I need to add, that I am not using “Convert Report Filters” and when I am “slicing” my measure, I link to the cell where the filter is.

BUT

When I convert report filter to formula all cells are calculated correctly again.

Example:

=CUBEVALUE(“PowerPivot Data”,$C$3(link to the filter),”[Geo].[City].&[City1]”,”[Measures].[sophisticated measure]”) – returns 0 or Null for some filter content (For example for [Grp 800]).

=CUBEVALUE(“PowerPivot Data”,”[Customers].[CustomersGroup].&[Grp 800]”,”[Geo].[City].&[City1]”,”[Measures].[sophisticated measure]”) – returns correct numbers.

Is it a problem in the “sophisticated measure” formula or there is some bags or restriction for “Convert to formulas”?

Thank you in advance for your help.