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
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