PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

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

MAY 15 - 16

Foundations: Power Pivot & Power BI

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!

Overview:

  • 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
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

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!

Overview:

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

MAY 17

Level Up Series: Power Query for Excel & Power BI

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

Overview:

  • 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
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

 
A long time ago I did a post on using slicer selections in Excel formulas.  That technique only worked when you select single values on slicers, though – any more than one and the dreaded “(Multiple items)” foils your well-laid plans.

How to handle that multi-select case became a very common question – in email and posted as comments.

Awhile back I responded privately to one of those requests but haven’t had time to post the solution.  So here goes.

The Solution, Summarized

First, here’s a picture of the solution I came up with.  For fun, see if you can figure out what I’m doing just by looking at it:

image

Everything in green is visible to the final report consumer (or at least, you can choose to make it so).  Everything in grey is stuff you likely hide  – either by hiding columns or by placing on a hidden sheet.  The SKUID field is on the slicer (that is hooked to both pivots) and is also on the row axis of the hidden pivot, but is not included on the visible pivot.

Here’s the same spreadsheet, but zoomed in and with formulas visible:

image

I went ahead and uploaded this workbook so you can take a look in a hands-on manner.

CLICK HERE TO DOWNLOAD THE WORKBOOK

Note that this was a PowerPivot workbook originally but I think I nuked all the data out of it, so you won’t be able to manipulate the pivots.  No worries though – the formulas here are 100% of the technique and should work with any pivot.

I’m pretty sure I could simplify this a little bit if I tried, but probably not by much.  I eagerly await everyone’s constructive input Smile

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:

http://powerpivotpro.com/2012/11/better-way-to-catch-multiple-slicer-selections-in-a-formula/

  Subscribe to PowerPivotPro!
X

Subscribe

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

  1. Hey Rob – I got this UDF a while ago from somewhere on the internets that concatenates a range of cells:


    Function concat(useThis As Range, Optional delim As String) As String
    ' this function will concatenate a range of cells and return one string
    ' useful when you have a rather large range of cells that you need to add up
    Dim retVal, dlm As String
    retVal = ""
    If delim = Null Then
    dlm = ""
    Else
    dlm = delim
    End If
    For Each cell In useThis
    If CStr(cell.Value) "" And CStr(cell.Value) " " Then
    retVal = retVal & CStr(cell.Value) & dlm
    End If
    Next
    If dlm "" And retVal "" Then
    retVal = Left(retVal, Len(retVal) - Len(dlm))
    End If
    concat = retVal
    End Function

    Using this, you can setup a little function that concatenates the row label headers:


    Function ptHeaders(dummyValue As Variant) As String
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    ptHeaders = concat(pt.RowRange.Offset(1, 0), ",")
    End Function

    It would of course be better to pass the name of the pivot table instead of hard coding the reference, but don’t have time this morning to look that up. I’m sure it’s simple to do.

    Then just call =ptHeaders({reference to sales total from visible pivot table}) in your Output cell to get the list of SKUs selected.

  2. Because I have up to 8 slicers in my PowerPivots I like to save space and use a single formula based solution that limits the number of shown multiple selections to 4. Anything more and they get a “More than 4” message (obviously you can expand the formula to capture as many selections that make sense for the subject in question)
    Here is a sample formula for the slicer Region_Name:
    =IF(ISERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,5)),IF(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,1)=”all”,”All Regions”,”Region(s): “&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,1))&IFERROR(“, “&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,2),””)&IFERROR(“, “&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,3),””)&IFERROR(“, “&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Region_Name,4),””),”More than 4 Regions”)

    1. Your solution above is exactly what I am in search of, yet I cannot get it to work. When I do the following:

      =IF(ISERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,5)),IF(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,1)=”all”,”All Years”,””&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,1))&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,2),””))&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,3),””)&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,4),””)

      This will work for the first 4 slices made, once you select a 5th slice, you will see “False and the first 2 slices made” in the cell.

      But if I do this:

      =IF(ISERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,5)),IF(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,1)=”all”,”All Years”,””&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,1))&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,2),””))&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,3),””)&IFERROR(“,”&CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Close_Year1,4),””),”More than 4 Years”)

      Excel says there is an error in the formula.

      Not sure what I am missing.

      Any help is greatly appreciated.

      Ken

  3. Hi,
    In the uploaded file, for Formula Series 2, for the first row, the formula should be =IF(S5=””,4,0), instead of =IF(S5=””,1,0). If you don’t make this change, and if only one slicer is selected, then the Output value returns a 0.
    Nandita

  4. Rob: Why don’t you just check if the first thing in your hidden pivot is the same as the last thing in your hidden pivot? Something like this:
    =IF(INDEX(A:A,COUNTA(A:A))A2,"Please select a single country","")

  5. This solution may work for me in Excel 2010, but am now challenged with multiple interactive slicers. My goal is to have the MANUALLY selected filter results (from all slicers) appear on the top of my master pivot table report (in the report title).

    My question is: Is there any way (preferably not VBA) to tell if a filter is being used. Like: if filter has an item selected then 1 else 0.

    Any ideas might help spark another idea…

    Thanks, Denise

  6. Here is my somewhat verbose attempt to create a one-cell solution with a title and the selected slicers using an array formula. I am still using Excel 2010 so I am not sure if this necessary with Excel 2013 and newer. It works for me. Let me know if you find any bugs.

    The slicer values (from the report filters) are in the range B1:B3. You can use more or fewer. As noted before, it is an array formula, so make sure to enter with CTRL+SHIFT+ENTER.

    =IF(RIGHT(TRIM(“My Title”&SUBSTITUTE(SUBSTITUTE(IF(AND($B$1:$B$3=”(All)”),””,”: “&$B$1&”, “&$B$2&”, “&$B$3),”(All), “,””), “(All)”,””)),1)=”,”,LEFT(TRIM(“My Title”&SUBSTITUTE(SUBSTITUTE(IF(AND($B$1:$B$3=”(All)”),””,”: “&$B$1&”, “&$B$2&”, “&$B$3),”(All), “,””), “(All)”,””)),LEN(TRIM(“My Title”&SUBSTITUTE(SUBSTITUTE(IF(AND($B$1:$B$3=”(All)”),””,”: “&$B$1&”, “&$B$2&”, “&$B$3),”(All), “,””), “(All)”,””)))-1),”My Title”&SUBSTITUTE(SUBSTITUTE(IF(AND($B$1:$B$3=”(All)”),””,”: “&$B$1&”, “&$B$2&”, “&$B$3),”(All), “,””), “(All)”,””))

  7. UPDATE: I wrote the formula above to handle multiple slicers per Denise’s question but not multiple selections within a slicer (the original problem!). This is exactly what I need so the formula works for me but YMMV.

    Denise, I think is the test you want:
    =–IF($B$1″(All)”,TRUE)
    or array format C+S+E
    =–IF($B$1:$B$3″(All)”,TRUE) for range $B$1:$B$3 where the filter values are captured

    P.S. – I don’t know how to lock my quote marks to ANSI 34 when posting. Left displays as 147 and right as 148 on this web page. When copying from this web page, make sure to replace all quote marks with ANSI 34 quote marks (Notepad works fine) before using these formulas in Excel.

Leave a Comment or Question