Extracting static data from powerpivot without pivot tables

[masplin]

I started with an excel table as follows

User ID Static1 Static2

I linked to powerpivot and created a bunch of calculated measures so powerpivot window looks like

UserID Static Static2 Calc1 Calc2 etc

I want to output the powerpivot back into excel with the same headings just as rows of data. As far as I know pivot tables cannot output values like names just a count of names or similar. So far the only way I can do it is to copy the whole of the powerpivot window into excel and then format it, but all numbers come out as text.

Is there an intelligent way to output the powerpivot raw data?

 

[ruve1k]

If you are trying to get a PowerPivot table into Excel, then you can simply query it. The easiest way to set that up is to first create any pivot table based on you PowerPivot data. Then double-click the measures area to drill-through. This creates a table with a connection to your PowerPivot model. Then edit the command text by going to: Data > Connections > Properties > Definition tab > Command Text. Change it to something like

EVALUATE Table1

and then refresh it.

 

[masplin]

Ah perfect thanks a lot

 

[masplin]

Is it possible to only output certain columns by this method?

 

[ruve1k]

Sure.
Use the SUMMARIZE function.
http://technet.microsoft.com/en-us/library/gg492171.aspx

 

[masplin]

Not sure I'm using that correctly as just want the contents of the table not perform any function on it.

Tried

summarize(gyms,gyms[gymID],gyms[gym name])

As my table is called gyms. This says "An MDX statement was expected". do I need to have something that is summed?

thanks

Mike

 

[ruve1k]

evaluate summarize(....)

 

[masplin]

easy when you know how!!! Thanks so much all sorted now

Mike

 

[kirchh]

If you are trying to get a PowerPivot table into Excel, then you can simply query it. The easiest way to set that up is to first create any pivot table based on you PowerPivot data. Then double-click the measures area to drill-through. This creates a table with a connection to your PowerPivot model. Then edit the command text by going to: Data > Connections > Properties > Definition tab > Command Text. Change it to something like

EVALUATE Table1

and then refresh it.

I have a similar need, but I'm afraid I don't understand some parts of your instructions here. Please excuse my inexperience with PowerPivot (and, really, with Excel in general), but could you be more precise and detailed so that I can follow your directions? For example, you say to "double-click in the measures area to drill-through", but I can't figure out what the "measures area" is (told you I was inexperienced!).

Thank you very much for any assistance you can provide.

 

[ruve1k]

The area in the pivot table where the values are, as opposed to the row labels and column labels.
It might be easier to use DAX Studio to execute DAX queries against your PowerPivot model..