Gil Raviv, Austin Senseman, Rob Collie

Have you Heard This Joke:  Gil, Austin, and Rob (left to right) Walk Into a Bar…
(Yes, Rob’s hair was purple at the time.)
(Note from Rob:  and it’s still purple today!)

Intro from Rob

Well folks it’s an exciting time to be working in our field.  An ongoing upsurge in Power Pivot awareness coupled with Microsoft kicking the Power BI marketing machine into high gear means three things:  1) We are very busy.  2) We are having lots of fun.  3) We need more people.

(BTW, have you filled out our mini-application form?  That’s the primary route via which we add folks to our team, and we have also quietly started matching people with jobs elsewhere in the industry as well – connecting capable and under-utilized people with employers that need and appreciate said talent is another way to “increase the happy” in the world).

And people…  well, the people in our field are the BEST thing about it!  Years ago, when one of Microsoft’s competitors attempted to woo me away from the Microsoft orbit, I politely declined and cited “the Excel people” as the reason I was never leaving the MS ecosystem.  Not the Excel team at MS mind you, but the folks who USE Excel every day.  I love you folks.  Furthermore, I am one of you, even though I was formerly one of the MS folks.  As was Avi.  And Jocelyn.  And Scott.

And now…  Gil Raviv!  Just an amazing dude with tons of talent and energy, and also (until recently) a Microsoftie.  We are very fortunate to be able to add someone like him to the team – he brings world-class Power Query and M knowledge to the table, absolutely rocked my DAX interview puzzle, and offers a perspective and sense of humor that is unique.  In many ways he epitomizes that blend of hard skills (DAX, M)  + soft skills (people, comprehension) that we see as the future of analytics and BI.  Time for his first blog post!  Oh wait, he posted here a long time ago while he was still at MS…  oh well, let’s call it his first anyway for fun.

Take it away, Gil…

Joining PowerPivotPro, and the Star Wars Slicer Brain Teaser!

Hi everyone. My name is Gil Raviv. Some of you may know me from my blog, DataChant.com, where I share Power BI, Power Query & M tutorials. Two weeks ago I joined PowerPivotPro team as a Principal consultant, and I am tremendously excited about it.

It is not the first time that I’ve blogged on PowerPivotPro. My first blog post here was written when I was a Program Manager at Microsoft. I found a cool way to integrate LinkedIn data into Excel, and Rob gave me the opportunity to blog about it. It was that first blog post that had awakened the blogging monster in me, and paved my way in the days to come.

In today’s blog post, I would like to share with you a brain teaser on Power Pivot and Power Query, that you can try on yourself and/or colleagues (as long as they don’t read this blog, right?)

But before we drill down to DAX, M and in between, here is how I ended up on the glorious PowerPivotPro team…

The Microsoft Days

In December 2013 I joined Microsoft as a Program Manager on the Excel team. I was assigned with the mission to improve Power View in Excel Online and Excel 2016. Ironically, at the end, the main improvement was to “turn off the lights” on Power View, and remove it from the Excel ribbon (You can still turn it on though, more info here). As the Power View team moved their efforts to new cloud experiences, and paved the way for Power BI, I was lucky to switch roles and start working on Power Query.

Leading the integration of Power Query into Excel 2016 was an amazing experience for me, and I got to work with some of the most talented people I have known. We re-wired Power Query into the Excel Data ribbon. Power Query in Excel 2016 (now squeezed into a small ribbon chunk called “Get & Transform”) was no longer an Add-In, but a true Excel powerhouse, that gracefully plays its part inside the bigger Excel ecosystem (For example, you can run VBA to build new queries and edit their formula language. Read more on Power Query integration here).

Power Query is Now the Get and Transform Section of the Excel 2016 Ribbon

Power Query’s new location in Excel 2016.

During that assignment, I became obsessed with Power Query and M, and gave very little attention to Power Query’s bigger sibling, Power Pivot.

My Love-Hate Relationship with DAX

Well, it started as a hate relationship. While it was clear to me that Power Pivot and DAX were extremely potent, I was too lazy and undisciplined whenever I tried to learn DAX. Building relationships was easy enough. Performing row-level calculations was straightforward. But whenever I heard the word “CALCULATE” in the corridor or classroom, I shied away.

But the change started when I was working on a new template for Excel 2016 (read more here). I was stuck with a DAX measure and called one of my colleagues for the rescue. As I was jealously watching him striking my keyboard, and creating the necessary measures, I experienced the magic of Power Pivot for the first time. Determined to get it right this time, I decided to listen to my colleague’s explanation, and I finally got what was CALACULATE all about.

Calendar Template for Analyzing Meeting Data

My first real DAX Experience:  Building this Template Back at Microsoft

The next day, I started reading the first edition of Rob’s book, and gradually let the DAX-phobia fade away.

Sidetracking Tip: Power Pivot is 1000% more powerful when combined with Power Query. The two tools didn’t live in harmony in Excel 2010, but combined together in Excel 2013, Excel 2016 and Power BI Desktop, Power Pivot and Power Query redefine Self-Service BI, so upgrade your Excel and make sure you take Power Query into consideration next time you build new Data Models.

Leaving Microsoft and How I Ended Up Here

After two years at Microsoft, and playing a significant role in releasing the best (IMHO) feature in Excel 2016, I left Microsoft, moved to Chicago, and invested a significant portion of my time in blogging on Power BI, Power Query and Power Pivot.

I would probably stay at Microsoft on the Excel team if I could, but there was no development center in the area, and the best advice that I got from my managers was to contact Rob. So I did.

That was six months ago. At the time, I was still solely focused on Power Query, but Rob gave me some good advice: “Improve your DAX”. He also offered to send me a test in DAX, but at the time I was still psychologically ambivalent about DAX, and I was still waiting for my US work permit, so I didn’t really insist on getting that test.

Time flew by. I had been improving my DAX and when the US work permit was finally in my pocket, I was ready for Rob’s trial. So Rob sent me the test. It was shockingly challenging, and it took me three sleepless nights to pass it.  And here I am Smile

Wanna Know Rob’s Interview Test?

Sorry mates. You don’t really expect me to share Rob’s test, do you? And don’t try to seek it on Glassdoor either. Instead, I will share my own brainteaser.

The test starts with a kind of a silly challenge, but you need an end-to-end above-average experience to implement it.

The Brainteaser – Add an “ALL” Button to a Slicer

The idea for the test, came during one of my first consulting sessions, when I was asked the following question: How can we add ALL as a button in the Slicer, as illustrated below?

We Want a Tile/Button in the Slicer that Clears Filters and Selects All Items

Can we Add an “ALL” Button to Our Slicer that Yields the Same Results as the Clear Filters Button?

Most of you are probably asking “WHY?”. The Slicer already has the Clear Filter button at the top right corner, so isn’t it a bit silly? The client DID have a legitimate reason for needing it, but it was a pretty obscure and domain-specific need. For the rest of us, it still is an engaging puzzle!

Note from Rob:  aha!  But there IS a more general case in which we need this – when we turn off the Slicer Header, we lose the “clear” button, as pictured here:

A Slicer with the Header Hidden ("Display Header" Unchecked) Lacks the Clear Button!

If you turn off the Slicer Header, You Lose the Clear Filters Button – Making this Technique
More than Just a Nifty Brainteaser!

So let’s insist on having an “ALL” button, and require that the “ALL” button will be the top button. To make the test more interesting, let’s assume we already have a measure that summarizes a numerical column in the Fact Table, so we will need to fix that measure as well.

BTW, the entire test can be done on Power BI Desktop as well.

The Star Wars Slicer Test

I prepared an Excel workbook that you can use for the test. It is based on a Star Wars Web API that I often use for demonstrations (More info here).

You can download this workbook as the starting point for the test. It contains three tables in the Data Model:

  • Species
  • Planets
  • Characters

 

Each Star Wars character belong to a species, and has a homeworld (a planet). The three tables are connected as follows:

image

We use the following measure to estimate the average mass:

Average Mass := AVERAGE ( Characters[Mass] )

In the main worksheet we have PivotTable with the Planets, and the average mass of their Star Wars characters. Our main task is to add “ALL” as the top button in the slicer.

We Want a Tile/Button in the Slicer that Clears Filters and Selects All Items

To make the test challenging enough, and to be picky with our job candidates, I’ve added three queries (Characters, Planets and Species). We will expect the interviewee to do some M work to resolve the test.

Workbook Queries

The Solution

Spoiler Alert!!! You may want to take a break and try answering the test by yourself before you continue reading.

Part 1 – Append ALL to Species

We will add the following table to Power Query and append it to query Species.

Species ID
ALL 0000

There are multiple ways to do this step. If you want to test your candidate’s Power Query skills, challenge them to start with a blank query, as I will show you now:

Blank Power Query

Creating a Blank Query

In the Query Editor’s Formula Bar enter the following formula:

= {[ Species=”ALL”, ID=”0000” ]}

Power Query Step

In List Tools, Transform tab, click To Table

Power Query Step

Click OK in the next dialog…

Power Query Step

Expand Column1, uncheck Use original column name as prefix, and click OK.

Power Query Step

 

Rename the Query1 to ALL, and open query Species.

Power Query Step

Click Append Queries in Home tab.

Power Query Append

In the Append dialog, select ALL as the table to append, and click OK.

Power Query Step

Sort column Species in ascending order, and find ALL in the appended table. You can also see ALL as the second item in the filter. Which brings us to the next part, where we make sure that ALL will be the top item in the Slicer.

Sorting in Power Query

Sidetracking Tip: Power Query is an ideal tool to create a lookup table in your Data Model. You can use it extract the relevant column from the fact table and remove duplicates before you create the one-to-many relationship. Remember: Power Pivot is case insensitive, and Power Query isn’t. Make sure that all the values on your lookup tables are lowercased before you remove duplicates. Make sure you trim all the values in the lookup table before you remove duplicates. If you have values such as “customer1 “ and “customer1 “ (with a trailing space) Power Pivot will treat those values as duplicates.

Part 2 – Create a new column for the sorting order

Our task now is to “push” ALL to the top of the Slicer and keep the alphabetically sort order for all the other elements in the Slicer.

Let’s assume that we will never have a Species that starts with the letters “aaaa”, and proceed with the following steps:

While the Query Editor is still open on query Species, click Add Custom Column in the tab Add Column.

Power Query Step

In the Add Custom Column, set New column name as Sort Order. Next, paste the following formula to the formula box:

if [Species] = “ALL” then “aaaa” else [Species]

Power Query Step

When you are done, click OK.

Power Query Step

We can now go to Home tab, click on Close & Load drop down menu, and select Close & Load To...

Power Query Step

In Load To window, select Only Create Connection, and check Add this data to the Data Model. Click Load when you are done.

Power Query Load To

We finished the Power Query elements of the test. Congrats!!! Time to move to Power Pivot.

In the Power Pivot window, open the table Species, and select the column Species.

Power Pivot Sort By Column

In Home tab, click Sort by Column, select the column Sort Order in the drop down menu Column, and click OK.

ALL in the Slicer, but...

That’s is, ALL is now the first button in the Slicer. But the measure [Average Mass] is obviously not working as required when ALL is selected. We will fix it in the last part.

Part 3 – Fixing the measure

Our measure is still not “wired” to recognize ALL as needed. Our Characters table has zero rows with ALL as their species. In this part, we will change the original measure to recognize the scenario when ALL is selected.

Open table Characters and add the following two measures under [Average Mass]

[All Species Avg]:=

CALCULATE([Average Mass] ,
ALL (Species[Species])
)

[Avg Mass Fixed]:=

IF (

     CONTAINS(VALUES(Species[Species]),
Species[Species],
”ALL”),
[All Species Avg],
[Average Mass]

    )

The measure All Species Avg clears the Species values from its filter context, and measure the average for all the species.

The measure Avg Mass Fixed gets all the selected items in the Species slicer, by using the VALUES function on table Species and its column Species. Then we check if the slicer contains an item “ALL”, by using the CONTAINS function. Using IF, we apply the original Average Mass measure when “ALL” is not selected, and the new All Species Avg when “ALL” is selected.

We are almost done. Now you can go back to the PivotTable. Drag and drop Avg Mass Fixed into the Values pane in the PivotTable pane, and remove the original measure Average Mass.

Have to Put the "Magic" Measure on the Pivot Instead

It works!!!

End Result

 

One more time:  you can download the Start or Finish versions of the workbook at your convenience.

Conclusions

While the solution above is not recommended on real world scenarios, as we already have a Clear Filter button, I hope you will find it useful as an assessment test. Just ask your candidate if he knows the PowerPivotPro Star Wars Slicer test, and if not, well, that’s probably OK right?  Even Power BI Padawans can move entire planets.

I am looking forward to meeting you again on my future blog posts on PowerPivotPro or DataChant, and who knows, perhaps we shall meet next time you hire a PowerPivotPro consultant (here or here).

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

  1. Loved the blog post! And, a big PowerPivotPro advocate, “Welcome!”, to Gil.
    I found your “style” very easy to follow. Thanks so much.

    Confession: I went straight to your solution after giving the problem about a minute of thought. I was on the right track.
    “Blank Queries” are still a little scary, but I’m working on it! I probably would have opted for quick Linked Table, or a {Enter Data} option in PowerBI,

    I’m really looking forward to future articles, as I too am finding that the DAX fog is beginning to clear from my head.

  2. I can see a similar technique being very useful when you need one slicer to allow you to select either an individual species or one of a number of pre-defined groups of species.

    Note: The process of creating the query to merge with the species list can be simplified by using:

    = #table({“Species”,”ID”},{{“ALL”,”0000″}})

    There is then no need to transform the list to a table.

  3. I am attempting to use your solution, so that I can learn something new. However, I ran into an issue, hoping you can offer some insight. I am using Office 365.

    Here is the Syntax error msg when I attempt to add the measures to the All Species Avg. I copied your example and I get the following error message: Could this be due to version issue?

    [All Species Avg]:=

    CALCULATE([Average Mass] ,
    ALL (Species[Species])
    )

    [Avg Mass Fixed]:=

    IF (

    CONTAINS(VALUES(Species[Species]),
    Species[Species],
    “ALL”),
    [All Species Avg],
    [Average Mass]

    )

    ============================
    Error Message:
    ============================

    The syntax for ‘[Avg Mass Fixed]’ is incorrect. (DAX(

    CALCULATE([Average Mass] ,
    ALL (Species[Species])
    )

    [Avg Mass Fixed]:=

    IF (

    CONTAINS(VALUES(Species[Species]),
    Species[Species],
    “ALL”),
    [All Species Avg],
    [Average Mass]

    )
    )).
    The calculated column ‘Characters[All Species Avg]’ contains a syntax error. Provide a valid formula.

    ============================
    Call Stack:
    ============================

    at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)
    at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj, ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate, XmlaWarningCollection warnings)
    at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
    at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
    at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options)
    at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateEntryCollection.DoUpdate()
    at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateOptimizer.CommitUpdates()
    at Microsoft.AnalysisServices.BackEnd.SandboxTransactionAmo.CommitUpdates()
    at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.CommitUpdates()
    at Microsoft.AnalysisServices.BackEnd.DataModelingView.Update()
    at Microsoft.AnalysisServices.BackEnd.DataModelingView.UpdateAddedColumns(List`1 addedColumns, List`1 oldExpressions, IList`1 colIndices)
    at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean suspendImpact)
    at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions)
    at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

      1. Hi Karen and Gil,

        I got a similar error. I noticed that, when I copied and pasted the formula from this page, I got the wrong sort of quotation marks. Excel is looking for plain double quotes around the “ALL”, but (for me at least) the webpage is showing them as left/right double quotation marks (“ALL”).

        Karen, try typing the quotation marks in yourself and see if that fixes it.

  4. Great job, Gil! I’ve used this slicer trick in a client’s dashboard a few months back, but you beat me to blogging about it 🙂 I think this trick has several implications that deserve their own post though, so I’m still going to cover them in my blog 🙂

  5. Good morning from Spain.
    My solution:
    1) Duplicate query Species and rename it Species ALL.
    2) Insert row: Table.InsertRows(#”Renamed Columns”, 0, {[Species = ” ALL”, ID = “0”]}). I add a space before ALL, this way it will always be shown at the top of the slicer provided it is sorted in ascending order.
    3) Save query creating connection and uploading it to the model as disconnected table.
    4)MyAvgMas=
    IF(
    CONTAINS( ‘Species ALL’; ‘Species ALL'[Species]; ” ALL”);
    [Average Mass];
    CALCULATE(
    [Average Mass];
    INTERSECT( Species; ‘Species ALL’ )
    )
    )
    4)Eliminate silcer linked to table Species and Insert a new one linked to the new table Species ALL, sort it in ascending order and connect it to the pivot table.
    Congratulations for the blog and best regards.

  6. Hey Rob,
    You probably know this, but you are able to add the total number into a pivot chart. This is important if you want a stacked 100% chart for groups with a total number on the secondary axis. Keep up the awesome articles

  7. I went a different way entirely in Part 3. Instead of messing with the measures, I duplicated the Characters query, stripped out the [Species ID] column, replaced it with a new [Species ID] = 0 column (where 0 is the ID of All), and appended the Characters All query to the Characters query. Now when All is selected, it filters the new Characters table for Species ID = 0, which is all of the items in the original Characters table. Since there are no X-measures, the doubling up isn’t a bad thing.

    Thus the entire problem is solved in M, and the DAX is left alone.

Leave a Comment or Question