Chris Webb's BI Blog

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

Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS

with 15 comments

One problem I came up against recently is how to find out what has been selected on an Excel slicer connected to SSAS or a PowerPivot model. There are a number of blog posts showing solutions to this problem, both for scenarios where only one item has been selected and when multiple items have been selected, for example (look at the comments as well as the posts themselves):
http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/
http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

…but I’ve come up with a new, MDX-based approach that handles the multiple selection scenario no matter how many items there are in the slicer, and which I thought was worth blogging about. I was tempted to include this in my series on MDX for PowerPivot but since it’s equally relevant for SSAS users, and the MDX is a bit complicated for an introductory series, I thought I’d make it into a standalone post.

To describe this technique I’m going to use same PowerPivot model I describe here, which is based on the AdventureWorks DW sample database. First of all, create a new PivotTable with CalendarYear in a slicer and CalendarYear on rows and any relevant measure on columns:

image

Selecting a Year in the slicer means that that Year appears on rows in the PivotTable, and selecting multiple years means multiple rows are displayed, as you would expect. Next you need to add a new MDX calculated measure to the PivotTable. If you’re using SSAS you can simply add the calculation onto your cube or you can use the OLAP PivotTable Extensions addin; for PowerPivot users the OLAP PivotTable Extensions approach is the only option, so that’s what I’ll demonstrate. Right-click inside the PivotTable and choose OLAP PivotTable Extensions from the right-click menu, and then in the Calculations tab create a new calculation called SelectedYears with the following definition:

Generate(
Except(
  Axis(1)
  , {[DimDate].[CalendarYear].Defaultmember}
)
, [DimDate].[CalendarYear].Currentmember.Name
, ", "
)

image

…and click Add to PivotTable. The result will be a new measure that returns a comma-delimited list of the names of everything selected on the rows axis of the query, and therefore everything selected in the slicer:

image

How does this work? Well, first of all Axis(1) is used to return the set used on the rows axis of the query used to populate the PivotTable (incidentally, this is why it’s important to have another measure in the PivotTable – if it’s not there, the structure of the query generated by Excel will be different and while the MDX can be altered to handle this, some of the items in the slicer will be partially greyed-out). The set returned by Axis(1) will include the All Member of the CalendarYear hierarchy, so the Except() function is used to remove it; finally, the Generate() function is used to iterate over this set and return the comma-delimited list of member names. In this example the CalendarYear field on the DimDate table in my PowerPivot model has become the MDX hierarchy with the unique name [DimDate].[CalendarYear]; please read this post for some background on how PowerPivot objects map to MDX objects.

Now you have the PivotTable you can refer to the top-right cell in it (in this example it’s cell F3)  in it to return the list of years and hide the PivotTable itself; this allows you to create dynamic titles like the following:

="Sales For Years: " & F3

You can then also create other, new PivotTables and hook them up to the original slicer and they will all work as normal:

 image

Written by Chris Webb

October 22, 2012 at 7:44 pm

15 Responses

Subscribe to comments with RSS.

  1. Thanks. Great job!

    bloger7791

    October 23, 2012 at 2:30 pm

  2. Hi Chirss ,
    I am a frequent reader of your posts . Could you help me in this scenario .
    I am not able to figure out how to do it .

    Could you please give me a solution for the problem . I already posted the problem in MSDN .Below is the URL of this .

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/edfdb901-bd82-412e-86b8-67ad855d2496

    Any suggestions would be greatly appreciated . Thanks in advance .

    Rajesh

    October 26, 2012 at 1:18 am

  3. Chris, could you enable Onswipe plugin? TIA

    Marcin

    October 26, 2012 at 10:54 am

  4. Hey Chris,

    great post, thanks a lot for sharing. Tried to reproduce that for my Needs – it is working in general, the only prob is that i seem to have many rows (around 800), which finally leads to an mdx error. Guess this is a timeout: “server: the operation was cancelled because of locking conflicts”.

    When i filter on just some rows, everything works perfectly fine.

    Maybe the MDX Needs some tweaks – do you have any idea how get this fixed?

    Thanks so much,
    Stefan

    Stefan

    October 1, 2013 at 3:58 pm

    • Hi Stefan, is there any processing happening on the server while you are browsing the cube? That would be one explanation for the error message. Otherwise, it could be that you are hitting the maximum length of a string in MDX, which would be very difficult to work around – but even if you could work around this, you’d be returning a string that would be too long for a user to make any sense of.

      Chris Webb

      October 1, 2013 at 8:14 pm

      • Thanks for your quick reply Chris! I am not Aware of any processing Happening on the Server while browsing the cube. The reason why i am keen on doing that is that i want to have a Semikolon seperated list of all row items – in my case email aliases, that i am going to use for a email Distribution list. The row items (mail aliases) are changing over time, that why i want to have an automated way to get to the Semikolon seperated list.

        Not sure, maybe there is also a way to do that in a power Pivot data model via dax, but i found your solution pretty cool as i wanted to extend my query with some slicers.

        Stefan

        October 1, 2013 at 8:49 pm

      • Hmm, it’s hard to say then, but my suspicion is that you’ve hit the maximum string length.

        Chris Webb

        October 2, 2013 at 9:51 pm

  5. […] 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 […]

  6. […] 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 […]

  7. hi,

    i am trying to use your solution to get the values of the slicer, i am using the same way but i am getting “Axis(1) could not be referenced”

    What should i do ?

    Regards,
    Your help is highly appreciated

    Georges

    Georges

    July 17, 2014 at 9:15 am


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,296 other followers

%d bloggers like this: