Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using Slicer Selections In The CubeSet Function

with 8 comments

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:

image

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:
    image
  • 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.
    image
  • 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.

image

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…

Written by Chris Webb

June 20, 2014 at 10:59 am

8 Responses

Subscribe to comments with RSS.

  1. 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).

    Marco Russo

    June 21, 2014 at 7:39 am

  2. 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?

    gm

    July 31, 2014 at 12:14 pm

      • No, i meant in a format that could be used in Cubeset function.
        In part
        ” & SelectedYears & “

        gm

        July 31, 2014 at 2:07 pm

      • 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().

        Chris Webb

        July 31, 2014 at 5:21 pm

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

        gm

        August 2, 2014 at 9:30 am

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

        Chris Webb

        August 2, 2014 at 9:51 pm

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

        Chris Webb

        August 2, 2014 at 10:13 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,146 other followers

%d bloggers like this: