PowerPivotPro is Coming to Houston

April 17 - 19, 2018


**Use the discount code “3ORMORE” when signing up 3 or more people.

APRIL 17 - 18

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!


  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
PowerPivotPro Training Classes - Houston
PowerPivotPro Training Classes - Houston

APRIL 17 - 18

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!


  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.


Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!


  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
PowerPivotPro Training Classes - Houston
PowerPivotPro Logo

***UPDATE:  An even better technique is now posted here. The new technique is much less hassle.

Back in June, Kasper posted a trick which lets you detect a user’s selection in a slicer, and use that in a PowerPivot measure.  That’s a very useful trick, one that we employ all the time at Pivotstream.

But sometimes, that is overkill.  Sometimes, you just want to grab a slicer’s selected value and use it in an Excel formula, right there in the sheet.  Here is the simplest method we have discovered so far:

1) Duplicate the field as a slicer AND a report filter

First step is to take the field you want to use as a slicer, and add it to your pivot both as a slicer, and as a report filter, as in this simple pivot:

Using Slicer Values in Excel Formulas Step One
Date Field Dragged to Both Slicer and Report Filter

2) Observe that the Report Filter “Tracks” the Slicer

OK, now click a date in that slicer.  Look what happens to the report filter in the sheet:

Slicer and Report Filter Stay in Synch

Cool huh?  Since they are the same field, the report filter has to always be in synch with the slicer.  And unlike the performance penalty that can pile up with multiple slicers, duplicating a field like this will NOT make your pivot slower at all.

3) Use the Report Filter Cell in a Formula

Yeah, you probably see where this is going already:  now you can reference the report filter cell in a formula, like this:

Reference the Report Filter to Get the Slicer Selected Value

4) Clean up the visuals

Move the formula to a more centered location, change the font, and hide the row that contains the report filter:

Hide Report Filter Row But Formula Still Reflects Slicer Selection


  1. I haven’t tried it but I am pretty sure this will work with regular pivots, too, not just PowerPivots.
  2. If you select multiple items in the slicer, you will get the text “Multiple Items” in your formula instead of a single value.  UPDATE:  I have since written another post that covers this.  It’s not a pretty technique but it gets the job done.  Post is here:  http://powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/ (November 2012 update:  see the link below for a less clumsy method).
  3. When you really get started thinking about this, there’s really no limit to the cool tricks you can pull off.  I’ll show a few more specific examples over time, but I’m sure you guys will discover many cool tricks of your own, too.  Here’s some food for thought:  report filters aren’t the only way to get slicer selections into a worksheet cell.

Update, November 2012

I’ve since posted yet another way to do this that is probably the simplest I’ve personally used to date, and it handles multi select quite well too:


  Subscribe to PowerPivotPro!


Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 17 Comments

  1. I was playing around with slicers in Excel 2010 because I needed to retrieve some values from them.
    I had a slicer with a date in string format like yyyymmdd called Slicer_TIMEID
    to count the number of selected items I used :

    =CUBESETCOUNT(CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption””,4))

    to get the latest item I used :

    =CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,4),1)

    to get the first item I used :

    =CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,3),1)

    to get the latest 3 items I used an array formula :

    =CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,4),{1,2,3})

    this only worked horizontally ( don’t ask me why )

    to get this one to work vertically just use TRANSPOSE

    I hope this will be of any use to somebody

    1. Very nice!

      To make the array dynamic, use (from another slicer example):


  2. How can we use this technique when multiple values are selected? I get the text “Multiple items” and the formula errors out.

  3. I’d really like the answer to Push’s last question if anyone comes up with a way. I came looking to solve this question, but alas.

    1. Hi Dan! That’s a good post, thanks for sharing. I also updated the top of this post to link to my more recent post that uses the better (CUBE formula) technique. Readers of this post now have all kinds of options for better info 🙂

  4. Hi All,

    The above posts were really helpful.

    Is there a way that i can display default values of slicer in a cell without any slicer selection??

    Thanks you in advance.

  5. I greet AD,
    I would like to ask a question about extracting excel filter with slicer
    I created a table by creating a table (ALT + N + T)
    From there, I select a slicer to perform the filter, according to the values (each value gives a calculated table, that value is always the title)
    Now I want to click and slicer (For example, click on E100) is that it will automatically filter. Then the title box (for example, cell C7) will automatically get the value you click on the slicer is “E100”
    Looking forward to your help
    I thank you a lot !!!
    You can download the file at the following link to do:

  6. Hi – I have a unique problem with the cuberankedmember function. I have 2 slicers on my dashboard page. First one is a “Region” slicer (showing 4 regions – Europe, JAPA, LAC and US) and second slicer is a “Market” slicer (showing all unique markets belonging to the 4 regions). When I send the dashboard out, I select all 4 regions (in region slicer) and all 20 unique markets (in market slicer) – to get the global view and send it to my audience. When I do this, the cuberankedmember function for returning market names (selected in slicer) returns the “All” value which is fine & all good. However, lets say if I now select only a single region (Europe lets say), then in region slicer – Europe will become a darker blue color while other regions turn grey. In market slicer, where all markets were selected (when dashboard was sent out) the European markets automatically turn dark blue and other markets turn very pale blue (not grey) and the cuberankedmember still returns “All” value. Is there a way to get the cuberankedmember to return the values of only European markets now (the dark blue colored selection from market slicer) since Region selected is “Europe” only and ignore the markets showing in pale blue (which are linked to unselected regions in region slicer). Basically, can cuberankedmember set on Market slicer (which is dependent on region slicer) just return the values for those markets belonging to that region (slicer shows them as dark blue, while rest of markets turn pale blue)

Leave a Comment or Question