Skip to content
February 19, 2013 / Erik Svensen

Create a dynamic table from powerpivot data in Excel 2013 without vba

Inspired by Chris Web’s blog post about Dynamic DAX Query Tables in Excel 2013 – link – I have tried to create a dynamic table from PowerPivot data without using VBA so it would be possible to publish it on SharePoint and browse the model using Excel services.

So how could we do this – let’s start by adding some data from the AdventureWorksDW to a data model in the workbook.

We only need to create the connection and this will add the data from the database to the PowerPivot model.

The relationships will be created automatically in this case, and I add a measure to the FactResellerSales – Sales Amount:=SUM(FactResellerSales)

By using the CUBESET function we can create a set using an MDX expression, this will return a set containing all the members of the column ResellerName in the table DimReseller.

The CUBESETCOUNT function can calculate how many children there is in the set.

Be aware that when you look at the table in the PowerPivot window we can see that there actually is 701 members – This is because the Reseller names aren’t unique. In order to avoid this we could use the ResellerKey instead but in this example we ignore this but be aware of this you choose to use this method.

Let’s create a table.

So I created a simple table with a reader and add the first row a formula that uses CUBERANKEDMEMBER to retrieve the first member of the set of Resellers.

To make the rank more dynamic – I calculate the rank by using the Current ROW of ResellerName minus the Row number of the Header row

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;$C$4;ROW([@Resellername])-ROW(Table1[#Headers]))

So in row 10 – the rank argument in the function will be 10 – 9 = 1

In order to make the table a list of resellers sorted after Sales Amount – I modify the formula in C4 to

=CUBESET(“ThisWorkbookDataModel”;”ORDER([DimReseller].[ResellerName].Children, [Measures].[Sales Amount],DESC)”;”The Set”)

To add other columns from the reseller table I add a header containing the Field name and add the following formula in the column.

=CUBEMEMBER(“ThisWorkbookDataModel”;”FILTER([DimReseller].[“&E$9&”].Children,([DimReseller].[ResellerName].[“&[@Resellername]&”], [Sales Amount])).Item(0)”)

By specifying the MDX expression “FILTER([DimReseller].[“&E$9&”].Children,([DimReseller].[ResellerName].[“&[@Resellername]&”], [Sales Amount])).Item(0)” only one member will be returned and will be the field specified in the header.

This formula can then be copied to other columns and by entering a valid column header the corresponding field will be retrieved from the data model.

To finish the dynamic table you can copy the formula down as many rows as you want to retrieve and add a slicer to the table – the cool new Excel 2013 feature.

Here is a link to the example – http://sdrv.ms/154zlUi

 

Advertisements

2 Comments

Leave a Comment
  1. Erik Svensen / Aug 26 2013 8:49 am

    Hi Tania,

    Will keep that in mind for my future posts.

    BR
    Erik

Trackbacks

  1. VBA to Read Entire PowerPivot Column

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: