PowerPivotPro

TUCO:  “There’s no name on it!”

BLONDIE:  “Well, there’s no name here, either.”

Ode to YouTube HD

When I used to work on Bing and would look at search logs, I was always puzzled by some of the search strings that went by.  Well today I gave them one of my own –“Clint Eastwood holding rock,” among others.  I hope that confuses someone.

YouTube in HD, though – you are my new best friend for finding obscure screenshots from movies.

The problem from last time

Remember my Rank report that started with #2?

Well, the reason for that is that I have some unknown players.  I’ve been filtering them out using the Row filter feature, shown here at right.  Note the last checkbox in the list, with the blank name unchecked.

(Also note the appearance of Zeron Flemister, two checkboxes from the bottom.  My friends and I had a tradition of always picking him in the last round of fantasy drafts just because we thought his name was so funny.)

Anyway, if I clear that filter and try to include that blank player in the pivot, I get the lovely error below.

Actually, that’s a pretty good error string, since it tells me pretty much exactly why the measure failed to evaluate.

Question 1: Where do the blank players come from?

My first hunch here was that my CleanPlayers[Full Name] column contained some blank values.

But then I remembered that the whole point of the CleanPlayers table was to make sure all unnamed players were removed.

So if that column does not contain any blank player names, why on earth do I get a blank player name in the list when I put that column in my pivot?

Answer: The Plays table sneaks them back in

“They must have found another way in, something we missed!”

-Ripley

(Best picture I could find.  Seriously.)

Here’s the thing:  when I removed all of the “blank name” players from my Clean Players table, I didn’t go back and remove all rows from the Plays table that involved those players.

So those rows from the Plays table now point to *nothing* in the Clean Players table.

To demonstrate, I sorted both the Plays and CleanPlayers table by [PlayerID]:

The first player in the Plays table is ID=4.  The first player in CleanPlayers is ID=70.

So when I add a measure from the Plays table (like Rushing Yards, or even my Rank measure), that forces a blank/unknown item to appear in my Clean Players list, even though there aren’t any such blank rows in the CleanPlayers table.

Step one of the fix:  find the error

OK, I spent a LONG time trying to figure out WHERE the error occurred in my measure.  Along the way I sent mail to my buddies in Redmond and begged them for a “step into” debugger feature.  But I know that it’s an incredibly difficult feature to build.

Let’s make a long story short.  Remember that second filter clause from the last post that I wasn’t sure was needed?  The one that made sure we weren’t comparing a player to himself?  Well, it’s actually the place where the error occurs, too:

   Players[FullName]<> Values(Players[FullName])

I remove that from the formula and now just have this for my rank measure:

   COUNTROWS(
     FILTER(
       ALL(Players[FullName]),
       [RushYards](Values(Players[FullName])) < [RushYards]
     )
   ) + 1

And that no longer errors with the unknown player in the pivot:

Good news: not only does the measure no longer error, I verified that it returns the exact same results for all 4,000 players as the measure did before I removed the second clause.  It was like my measure had appendicitis – a critical problem in a needless organ :)

Bad news:  That unknown player is still grabbing the top rank, demoting Edge to 2.

Good news:  I figured out how to fix that.

Bad news:  Man is the fix ugly.  I’m going to see if there’s a better way before I blog about my Rube Goldberg solution.

I really did intend to finish this off for you guys today but don’t want to spread bad methodology.  Stay tuned.

“Number One, I order you to take…  a number two.”

-Captain Jean-Luc Butthead

Before I get started, do you have any idea how hard it is to find cartoon images from the pre-Internet era?  I just lost ninety minutes, first looking for the Far Side cartoon of “Custer’s Last Group Photo” (where everyone is holding up the “we’re number 1!” finger – get it?) and then struggling to find a version of the Beavis and Butthead image above at a better size.  No dice.  OK, enough of that…

DAX v1 has no RANK() function

That’s right.  If you want to write a pivot measure that tells you where a particular store, or product, or customer ranks according to another measure, and according to current filter context, well, a RANK() function would be nice.  But there wasn’t time to implement it in PowerPivot v1.

Never fear, there’s a workaround…

OK, yes, there’s a workaround – the FAQ says so right here. 

But “never fear” might be an overstatement.  In fact, I should pause right here and say…

PowerPivot is simple, this technique is not

I’ve been doing this blog since October.  I’ve covered a lot of ground.  If you read this post and fear that you have to understand it in order to get great results with PowerPivot, well, then I’ve done you a great disservice.

Because in many ways, PowerPivot is actually easier than Excel itself.  If you are new here, I strongly recommend going back to the Great Football Project and starting from the beginning.

OK, back to the power technique

I don’t know about you, but personally, my first impression of the formula referenced in the FAQ sounded a lot like “double yoo tee eff.”  Check it out:

  COUNTROWS(
      FILTER( 
          ALL(DimCustomer[CustomerName]), 
          DimCustomer[CustomerSales]
              (Values(DimCustomer[CustomerName]))
                   < DimCustomer[CustomerSales]
          && DimCustomer[CustomerName] <>  
                   Values(DimCustomer[CustomerName]) 
      )
  ) +1

Explaining how it works

That formula has been bothering me for a long time.  I knew it wasn’t going to be easy to break it down, but on our little DAX adventure together, we must leave no stone unturned, even the really heavy ones.

So today, I dove in.  And whenever I get uncomfortable, I run to familiar ground.  Which means, of course, oh yes…

The Great Football Project Returns!

Quick!  Someone get me some early-2000’s running backs!

Phew, I feel better already.  OK, let’s dig in.  A long time ago I defined a [RushingYards] measure using the CALCULATE function.  Let’s park that in a pivot:

 
I want to write a measure that gives me the rank of each player, by Rushing Yards, no matter how I have sliced or filtered my pivot.

First step:  convert the sample formula over to my table/column names

When I substitute my football workbook’s names into that formula, I get:

   COUNTROWS(FILTER(ALL(Players[FullName]),
   [Rushing Yards](Values(Players[FullName]))
   <[Rushing Yards] && Players[FullName]<>
   Values(Players[FullName])))+1

I won’t bother pretty-printing that monster just yet, because I’m going to dissect it step by step.

Outside functions:  COUNTROWS + 1

The formula is basically:

   COUNTROWS(all kinds of crazy stuff) + 1

Which starts to make a little bit of sense, if you’re feeling sleuthy.  Really, what this is doing is:

   COUNTROWS(# of Player Rows where Player has more yards) + 1

Make sense?  Find how many players outrank the current player, add one, and that’s your rank.  OK, so how does the middle stuff determine the number of players that outrank the current player?

Inside functions – FILTER and ALL

   FILTER(ALL(Players[FullName]),
   [Rushing Yards](Values(Players[FullName]))<[Rushing Yards]
    && Players[FullName]<>Values(Players[FullName])))

Recall that the syntax for FILTER is:

   FILTER(Table Expression, Filter Clause)

In this case, our table expression is:

   ALL(Players[FullName])

Which makes sense.  Remember, on a particular row of our pivot above, the Players table is going to only contain the row from that player, for purposes of measure calculation. 

Ranking Edgerrin James against himself, for instance, isn’t very interesting, unless you want to compare the Colts version to the Cards version, or heaven forbid the Seahawks version.  (The wear and tear on RB’s really is astounding).

So in order to have any players to rank the current player against, we need a table where the filter has been cleared.  That’s why we use ALL.

That leaves us with the Filter Clause, and it deserves its own section.

Inside Functions – the “Filter Clause” parameter to FILTER

OK, so we have a table that consists of all player names.  And we want to filter that down to just those players that have more Rushing Yards than the current player.

The FILTER function goes through that table of player names, one by one, and for each row, evaluates the filter clause to determine whether that row is “in” or “out.”

Our filter clause is really two clauses connected by the logical “and” operator – &&.  So a row must meet BOTH criteria to be included.

First clause:

   [Rushing Yards](Values(Players[FullName]))<[Rushing Yards]

This gets tricky, so let’s anchor it in an example.  Let’s say we are evaluating this measure for the very first row of our pivot, Edgerrin James:

And the FILTER function is going to step through  our table of all player names, at right.  (There are about 4,000 players in my players table).  So, FILTER starts with Rabih Abdullah.

The right side of the clause above, [Rushing Yards], is the [Rushing Yards] measure, evaluated in the context of this source row, Rabih Abdullah.  Well, he totaled 172 yards in his rushing career.

But what about the left side of the clause:

  [Rushing Yards](Values(Players[FullName]))

That uses the context from the *pivot*, not the source table.  the VALUES function returns the list of valid values of the specified column, according to current pivot context.  Since the specified column is on the rows axis of our pivot, it only returns one value in this case, which is Edgerrin James.

Edge has 9,842 rushing yards.  So the clause becomes:

   9842 < 172

Which is of course FALSE.  Rabih’s row gets excluded by the FILTER function.

Wash rinse, repeat.  And then repeat.

FILTER then moves on to the next source row:  Khalid Abdullah.  The pivot context remains the same (Edge James), so the left side of the comparison is still 9842.  Khalid has less than that, so again, excluded.

Well, in my data set, no one has more yards than Edge, he’s the top dog in my data set.  So when FILTER finishes its pass, there are no rows left included.  COUNTROWS then returns 0, the + 1 from the end of the formula gets added, and Edge gets his proper rank of 1.

The measure then moves on to the next pivot context, the second row, which is Curtis Martin:

And then FILTER starts over again, running through the entire Players table again, row by row, starting with our friend Rabih.

Two loops tucked inside each other.  Wow.

The other half of the filter clause

Remember this?

    && Players[FullName]<>Values(Players[FullName])))

That just makes sure we don’t count the player against himself for purposes of rank.  Off the top of my head, I’m not sure that’s 100% necessary.  I’ll have to noodle on that a bit.

OK, let’s see it in action

I add my new measure to the pivot, and I get:

What the heck?  Is my measure broken?

No, it’s not.  I’ll explain what’s going on in my next post, since this one has run so long already.

In the last post I was struggling with horribly-designed source tables.  To be honest, I never found a way to relate those two tables to each other, even through other intermediate tables that initially seemed promising.

But I DID find two columns in my Plays table itself that saved my bacon – [OffensiveTeamScoreAtPlayStart] and [OffensiveTeamScoreAtPlayEnd].

A simple calc column subtracting the two and now I have [PtsScoredOnPlay]:

  =[OffensiveTeamScoreAtPlayEnd]-[OffensiveTeamScoreAtPlayStart]

And that appears to give me what I want – see the picture at right.

(I was a little suspicious at first that the Offensive team could score 2 pts, since only defenses can score safeties, but then I realized these were 2-pt conversions by the offense – see how subtle “business rules” can be?)

OK, so with that calc column in hand, and my knowledge that all Touchdowns score 6 pts, I can go back to my PivotTable and add a measure:

   =CALCULATE(
         COUNTROWS(Plays),
         Plays[PlayTypeName]="RUN-Run",
         Plays[PrimaryRoleName]="Runner",
         Plays[PtsScoredOnPlay]=6
    )

Dissecting the Formula

I’ve broken the formula into separate lines above.  Let’s go line by line.

1. The first line is just the CALCULATE function.  I have previously described CALCULATE as a supercharged version of SUMIF.  That bears repeating.  Think “improved SUMIF.”
2. The second line is the expression to be aggregated.  You are not limited to SUM.  In fact you can place many custom expressions here.  In this case I am using the COUNTROWS function.  I will explain that below.
3. The remaining 3 lines are just conditional filters, like the “if” part of SUMIF, or more accurately, SUMIFS, since there are more than one.  Notice that the last one filters down to just plays that scored 6 pts, which are touchdowns.  The other two filters insure that this was a Rushing play, and the current player was indeed the one carrying the ball.

COUNTROWS is another good (new) friend

Longtime PivotTable users are familiar with a common trick.  When you want to count the number of rows from your source data that meet various criteria (in a PivotTable), you find a column in your data that is guaranteed to be unique, and then you add that field to your Values area, and change the “Summarize By” to count.

And if you didn’t have a column that contained unique values, well, you created one.

With COUNTROWS, you no longer have to do that.  COUNTROWS(<TableName>) works every time.

Tying it all together

OK, then, the formula above says:  “In my Plays table, count the number of rows that are rushing plays, where the current player is the one carrying the ball, that resulted in 6 pts.”

Does it work?  You bet.  PivotTable on left, ESPN on right.  (Priest Holmes, 2004).

OK, back to Sexy!

In the next post, I can now return to what I was originally trying to do, which was something semi-mind blowing…  if you’re an Excel geek anyway :)

Ok, in the last football post, I had written the following measure:

Which basically means, “sum up the [Yards] column but only those rows with [PlayTypeName] = “RUN-run” “

Now we get to find out if it works :)

I need some real-world data that I can validate against.  Out on NFL.com I can find historical player stats.  Let’s look at one of my favorite players from recent years, Priest Holmes.  He amassed a lot of Rushing Yards and should be a good sample.  Here are his statistics by Game for 2004:

OK, so let’s make a PivotTable that just shows Priest’s 2004 Rush Yards by Game, and compare that to the Rush Yards column from above:

                           My Pivot Table                                      From NFL.com

Bingo!

“You know sometimes I even impress myself?”

-Han Solo

An exact match!  That might seem a bit bland to you folks out there, but to me, it is VERY exciting.  I’ve got 40+ tables, a Plays table that is loaded with crazy complexity, no database training whatsoever, and yet…  I now have a measure that agrees, DOWN TO THE YARD, with NFL.com!

Wahoo!  In all honesty, when I started this project, I had no idea how far I could go.  I intentionally chose something that *might* defeat me.

I’m feeling pretty darn optimistic now, though :)

I’m suspicious, however…

I recall that my Plays table contains multiple rows per play, so that, for instance, it can capture data about the *defensive* players involved in a play:

Right there, a single play, three rows.  One for the runner himself, one for the player who tackled him, and another for the player who assisted the tackle.  That will come in handy when I get started looking at defensive stats.

But for now, I suspect that means defensive players are getting credited with rushing yards, too.  When I filter to Antoine Winfield, the Assister from above, my PivotTable confirms my suspicions:

What we are seeing there is the total net yards of running plays in which Antoine Walker was involved in tackling the runner.  I don’t want that.

But all I have to do, then, is add another clause to my CALCULATE function.

And now the pivot table shows:

Priest Holmes is unchanged.  Antoine Winfield now has no rushing yards.  Perfect!

CALCULATE is good.  CALCULATE is your friend.

“OK Rob, a whole post just so you can make ONE change??”

Yes.  I spent more time than usual on this.  Here’s why:  Everything above took me less than 10 minutes in real time.  It took far longer to capture the screen shots than simply to blaze through it.

But when I worked with a BI consultant, a few years back, the same exact iteration took about a week.

How do we account for the difference?  Is it because the consultant produced better results?  Was the Rushing Yards measure more accurate or robust than mine?  Was it somehow more formalized, more robust?  Or was the consultant not very good?

The answers are no, no, no, and an emphatic “no.”  (The consultant was fabulous, akin to godlike.  There’s that word again – “akin.”  Why do I keep saying that?)

The real difference, as I’ve said before, is that with PowerPivot, the “modeler” and the “business user” are the same person.  I’m the one writing the expressions, and the one who knows the most about what I want, because I know the “business” (football, in this case) inside and out.  Iteration in one person’s head is blazingly fast.

1. Finding the comparison stats on NFL.com – Only the business user knows where the best validation data sets are.
2. Creating the pivot for comparison purposes – even just choosing my player for comparison reflected business knowledge. 
3. Realizing that I likely was incorrectly counting defensive players in my measure – because I had access to the source tables and the “business rules” in my head, I spotted this problem before it ever made it into a report.

Seriously, this was like a trip down memory lane, on hyper fast forward.  I’d get a cube from the consultant, build some pivots, see that things were not accurately reflecting football rules, point out the problem, wait for the next version, repeat.

Not anymore :)

“Are you saying we don’t need BI Pros anymore?”

No, I am NOT saying that.  Even in this football example, I am cheating.  The original data from STATS arrived as a jumble of text files.  The schema was terrible.  Many of the required attributes (like the score of the game on a particular play) were completely missing.

There was a TON of work, done by the BI professional, to get from that horrible mess to the 40+ tables that I am working with now in PowerPivot.  And there is no way, no way at all, that I could do that myself – not back then and not today.

In terms that an MS BI Pro understands, the Integration Services work remains.  In fact, it becomes even more important, since you need to make the resulting schema not just work for people like you, but also for people like me :)

But the Analysis Services work – you can start sharing that with the Excel business users.