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

 

One Slicer That "Controls" Other Slicers.  Multiple slicers "get set" when the user makes one click.

Got this question yesterday from the DJ Monkey…

Let’s say you have three slicers:  Percentile, Quarter, and Category:

image

For Bikes, You Always Want to See the 70th Pctile in Q2

And you find yourself selecting the same combinations over and over again.  Above, you selected 70, 2, and Bikes – because Quarter 2 is essential for Bike sales, and 70 is the percentile you are most interested in for bikes.  (Note that this percentile drives conditional formatting in this example – see this post for details).

When you look at Accessories, you always want to see Q1 instead, and set the Percentile threshold to 60.  And for Clothing, it’s a different set as well:

image

But for Accessories, You Always Want to See the 60th Pctile in Q1

image

And for Clothing, It’s 80 and Q4 That You Always Want to See

Is that too repetitive for you?  Three clicks with a brief pause in between each makes you feel like a monkey?  There’s no satisfying you, is there? Sheesh Smile

But hey, there’s a way to essentially set multiple slicers in one click, without VBA, as long as you know ahead of time what the desired combinations are.

Yet Another Disconnected Slicers Trick

First I create a new table, which I have named MasterSlicer:

image

Behold, the MasterSlicer Table!  OK yeah it doesn’t look like much.

And for temporary illustration purposes, I’ll create a new pivot and put the Label column on a slicer:

image

Label Column from MasterSlicer, Used as a Slicer

And then I write three “harvester” measures that detect what the user has selected on the Master Slicer:

[Selected Category] =
MAX(MasterSlicer[Category])

[Selected FiscalQtr] =
MAX(MasterSlicer[FiscalQtr])

[Selected Pctile] =
MAX(MasterSlicer[Pct])

So when I select “Bikes 70 Q2” on the slicer, I get the following in the pivot:

image

The Harvester Measures Just Return the Contents of the Selected Row in MasterSlicer

So far so good.

Step 2:  A Profit Measure That Respects MasterSlicer

Returning to the original pivot, My [Profit] measure needs to be modified now.  (I could also create a new measure, and I probably would, but for now let’s just modify the one I have).

[Profit] =
CALCULATE( SUM(Sales[Margin]),
           FILTER(Products, Products[CategoryID]=
                           
[Selected Category]                 ),
           FILTER(Calendar, Calendar[FiscalQuarter]=
                            [Selected FiscalQtr]                 ),
           FILTER(CFMinBar, CFMinBar[Pct]=[Selected Pctile])
          )

Results

Ok, now you have a single slicer that does the work of three:

One Slicer That "Controls" Other Slicers.  Multiple slicers "get set" when the user makes one click.

Important Notes

1) First, I really messed up by using the Conditional Formatting slicer as one of the three slicers in this example.  It needlessly complicated an otherwise simple concept.  Go read that original post if you’re interested, but I’m going to leave it out of this post from here on.

2) You CANNOT Use this technique in conjunction with the normal slicers.  It is one or the other.  Even when there is no selection made on the MasterSlicer, the MAX() logic in the harvester measures kicks in and picks 80, 4, and 3, and then the FILTER()’d version of [Profit] respects that.

So you either go back to using the normal [Profit] measure and the normal slicers, or this disconnected MasterSlicer and the FILTER()’d version of [Profit] – this is why I would normally create a new version of the [Profit] measure rather than modifying the one I have.

Visualizing this Technique

image

I use this illustration technique a lot in the book.  Filter/Lookup/Dimension tables displayed above the Data/Fact tables, with orange arrows indicating the direction that filters flow.

Dotted lines indicate “artififical” or “virtual” relationships – relationship-like behaviors that are expressed in measure formulas (using FILTER).

So the user’s selections on MasterSlicer flow through to Products and Calendar via FILTER() measure logic, and then those filters flow through to Sales (where [Profit] is) via normal relationships.

What About Text Columns?

Originally, I did not have a [CategoryID] in my Products table, I just had the [Category] column, which was text.  And for my harvester measure, numerical functions like MAX() don’t work with text.

In this case I used a SWITCH() to add a calculated column:

image

In cases where you have too many text values to write a reasonable SWITCH(), I suppose you could use text measures with VALUES() and skip the creation of this column, but that’s a topic for another day.

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

  1. Hi Rob,

    I have a text column on a disconnected table that I want to use as a slicer. There are too many values to use Switch as described above (and the text values are subject to change by the user) … I cannot use Max() to determine the value selected in the slicer. Did you ever get to write a blog on using the Values() function?

    Hope this does not get in the way of Thanksgiving!

    Regards,

    Ted Murphy

    1. Hi Rob,

      Please disregard the above comment re text column. I revisited the Disconnected Slicer Techniques in the Advanced PowerPivot Lessons and got a solution.

      Thanks,

      Ted.

Leave a Comment or Question