I had an interesting challenge from a customer yesterday – one of those problems that I’d known about for a long time but never got round to working out the solution for…
Consider the following PivotTable, based on a PowerPivot model using Adventure Works data, in Excel 2010:
It shows the top 10 products by the measure Sum of Sales. There are two slicers, and the top 10 shown in the PivotTable reflects the selections made in the slicers. All of this works fine. But what if you want to use Excel cube functions to do the same thing? You can write the MDX for the top 10 products quite easily and use it in the CubeSet() function in your worksheet, but how can you get your MDX set expression to respect the selection made in the slicers?
The solution to this problem is very similar to the trick I showed here – finding the selected items in a slicer is not easy! Here are the steps I followed to do it:
- Add the slicers for EnglishOccupation and CalendarYear to a new worksheet
- Go to Slicer Settings and uncheck the box for “Visually indicate items with no data”
- Add two new PivotTables to the worksheet. Connect one to the EnglishOccupation slicer and put EnglishOccupation on rows; connect the other to the CalendarYear slicer and put CalendarYear on rows.
- Use the OLAPPivotTableExtensions add-in (which you can download here) to add new MDX calculated measures to each PivotTable. For the EnglishOccupation PivotTable call the measure SelectedOccupations and use the following MDX:
SetToStr(Except(Axis(0), {[Customer].[EnglishOccupation].[All]}))
This expression does the following: it uses the Axis() function to find the set of members selected on what Excel thinks of as the rows axis in the PivotTable (actually the MDX columns axis), then uses Except() to remove the All Member from the hierarchy (which Excel uses for the Grand Totals) and then uses SetToStr() to take that set and return the string representation of it. Do the same thing for the PivotTable showing CalendarYear too, calling the calculated measure SelectedYears; the MDX in this case is:
SetToStr(Except(Axis(0), {[Date].[CalendarYear].[All]}))
This is what the EnglishOccupation PivotTable should look like:
- Next, to make things easy, use Excel formulas to get the values from the top cell inside each PivotTable into cells elsewhere in the worksheet, and give these cells the names SelectedOccupations and SelectedYears.
- Then enter a CubeSet() function into a new cell using the following formula:
=CUBESET(
"PowerPivot Data",
"Topcount(
[Product].[EnglishProductName].[EnglishProductName].members,
10,
Sum(" & SelectedOccupations & " * " & SelectedYears & ",[Measures].[Sum of Sales])
)",
"Top 10 Set")
What this does is use the TopCount() function to find the top 10 Products, and in the third parameter of this function which is the numeric expression to find the top 10 by, it crossjoins the two sets of selected occupations and selected years and then sums the output of the crossjoin by the measure [Sum of Sales]. - Last of all, build your report using the Excel cube functions as normal, using the CubeRankedMember() function to get each item from the top 10 set created in the previous step.
You can download my sample workbook here.
The bad news about this technique is that it doesn’t work in Excel 2013 and Power Pivot. It’s no longer possible to create MDX calculated measures on Power Pivot models in Excel 2013, alas. It will work if you’re using any version of Excel from 2007 on against Analysis Services and, as I show here, Excel 2010 and PowerPivot. If you are using Power Pivot and Excel 2013 it might be possible to create a DAX measure to do the same as the MDX I’ve used here (I’m wondering if the technique Jason describes here will work). It would certainly be possible to use CubeRankedMember() to find each item selected in the slicer, as Erik Svensen shows here, and then use Excel formulas to find the MDX unique name for each selected member and concatenate these unique names to create the set expression that my calculated measures return, but that’s a topic for another post. This really should be a lot easier than it is…
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
You might use the TOPN in DAX, obtaining a table in Excel, but it’s an issue to move back and forth from DAX to MDX (which would be useful to use other CUBE functions). If you just need a table in Excel with TOPN, using a DAX query is probably the easiest way (not easy enought for an Excel user, I agree).
Is it possible to create MDX measure (Selected Years), so you can create
=Cubevalue(“Model”, SlicerYears, “[Measures].[Selected Years]”)
that would return selected years same as Pivot Filed, but than you would not need a PivotTable?
Or the rows on PivotTable are the only way because of Axis(0) set?
Do you mean like this? http://cwebbbi.wordpress.com/2012/10/22/returning-selected-items-in-an-excel-slicer-using-mdx-in-powerpivot-and-ssas/
No, i meant in a format that could be used in Cubeset function.
In part
” & SelectedYears & “
If I understand you correctly, it would be very like the code in the post I just linked to but you would need to use the SetToStr() function instead of Generate().
I was thinking about something like this:
=CUBESET(
“PowerPivot Data”,
“Topcount(
[Product].[EnglishProductName].[EnglishProductName].members,
10,
Sum(” & CUBEVALUE(“PowerPivot Data”, Slicer_Years, “[Measures].[Selected Years]”) & “,[Measures].[Sum of Sales])
)”,
“Top 10 Set”)
Where the part:
CUBEVALUE(“PowerPivot Data”, Slicer_Years, “[Measures].[Selected Years]”)
would return selected years from the slicer: {[Date].[CalendarYear].&[2001], [Date].[CalendarYear].&[2004]}
That way we wouldn’t need pivot tables.
Yes, so here you’d need to use the Generate() function in the way I said. I’m on holiday for the next few weeks but this is a good subject for a blog post when I’m back.
Hold on, I’m getting confused (that’s the problem with answering comments when you’re on holiday). Yes, I think you’re right, using CubeValue() should work here.
I am trying to dynamically sort a set based on slicer selection. I have different “companies” that have different fiscal year start months.
I have exactly what I want in a pivot table including selectedsupplier, but I can’t figure out how to pass that to the cubeset function.
below is not working:
=CUBESET(“PowerPivot Data”,”TopCount([Time].[MonthName].members, 12,SUM(” & “[Time].[SupplierName].&[” & Slicer_SupplierName & “]” , “[Time].[MonthSort]”))’,”MonthSet”)
Which version of Excel are you using?
I think I have figured out the above. I ended up creating the pivot table and referencing the cell location as you described.
The issue I’m having now, is that say you have two slicers Category and Sub-Category. When user selects category, the sub-categories are automatically-selected in the slicer, however the SetToStr formula (described above) still shows the string for all Sub-Categories. This is fixed if you explicitly click on each sub-category, however from a usability standpoint the formula should auto-populate. In my example there are hundreds of sub-categories and clicking on each one is not feasible.
I figured out how to do it with separate pivots for each slicer dimension and then building the mdx string described above. You only need the string for the lowest level of granularity slicer.
Now if I could figure out how to do this without topcount involved. Topcount slows down the query tremendously…
Were you able to figuer out the performance hit coming with the TopCount? I’m having exactly the same issue. Thanks.
Topcount() can be a performance problem simply because of what it does. It isn’t always possible to optimise it…
I got rid of it altogether. I made a separate pivot table in another sheet and used the top n filter, then sorted by the measure. Then I referenced the cells in my dashboard sheet. It’s all connected to the slicers and works great.
I think it’s weird that the pivot table can determine top n so much more quickly than the mdx. Maybe the mdx needs a ‘nonempty’ or something, but I didn’t have the time to mess with it for much longer so I went with the pivot table approach.