PowerPivotPro

“…I always see the job through.”

-Angel Eyes

Yep, I said I would return and finish off the Rank measure.  Time to deliver.

 
(And this makes three straight Spaghetti Western movie quotes!)

Where we left off – Unknown ranks #1

Last time, I’d written a mostly-working version of the measure that ranks a player by his Rushing Yards value, and that doesn’t return an error when I leave the Unknown player in the pivot:

 
But the Unknown/Blank player is still messing up my rank because of all the small-time players it lumps together into a huge number of rushing yards – I want Edgerrin James to be first.

Must remove the Unknown player from rank consideration

This turns out to be pretty tricky.  Here’s the formula again:

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

As explained last time, that’s counting the number of players that have more rush yards than the current player in the pivot, and then adding one.

The heart of that formula is this comparison test – players only get counted if this evals to true:

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

I want to add a condition to that.  It’s not enough for a player to have more rush yards.  They also must NOT be Unknown/Blank.  So I want it to be something like:

   [RushYards](Values(Players[FullName])) < [RushYards]
      && PlayerIsNonBlank

Filling in that green part with a real formula is the trick.

Why not just remove the + 1 and be done with it?

Indeed, I could remove the “+ 1” at the end of the formula and voila! – all is right with the rankings.

But that’s only true right now, without the pivot filtered in any way.  There will definitely be cases where, as I slice the pivot down to narrow cases, no Unknown player recorded any Rush Yards.  And suddenly, the top real player would be ranked as –1.

“PlayerIsNonBlank” – the DAX solution

First I had to define a new measure, [NonBlank Player Count].  It is defined as follows:

   COUNTROWS(DISTINCT(CleanPlayers[FullName]))

It turns out that DISTINCT does not return Unknown members.  DISTINCT and VALUES are exactly the same function, except that VALUES includes Unknown and DISTINCT does not.  (Colin, sorry I never replied to your comment awhile back, but yes, that is the difference).

So that’s pretty handy.  When I am in the context of a real player, this measure returns 1.  And so I can use that measure inside my Rank measure, to weed out the Unknown player from rank consideration.  Replacing the green pseudoformula from above my condition is now:

   [RushYards](Values(Players[FullName])) < [RushYards]
      && [NonBlank Player Count] = 1

Which makes my overall measure:

   COUNTROWS(
     FILTER(
       ALL(Players[FullName]),
       [RushYards](Values(Players[FullName])) < [RushYards]
         && [NonBlank Player Count] = 1
     )
   ) + 1

Does it work?

Sort of.  Here ya go:

So… the numbers are right!  Edgerrin James is now #1 as he should be, and everyone follows after.

But that pesky Unknown player.  It won’t go away…  it’s like Rasputin or something.

It’s ugly, and I want my reports to be pretty.

Furthermore, look at the Grand Total:

Again, ugly.  The grand total cell should show nothing.

The fix:  making the measure return BLANK()

To solve this, I took the entire measure above and wrapped it in two nested IF’s:

=IF(COUNTROWS(VALUES(CleanPlayers[FullName]))=1,
   IF(NOT(ISBLANK(Values(CleanPlayers[FullName]))),
      <The entire measure formula from above>,
      BLANK()
   ),
   BLANK()
)

What that means is this:

1. If the current pivot context corresponds to exactly one player, AND that player is NOT a Blank player, then go ahead and evaluate the measure expression. 
2. If the current pivot context corresponds to multiple players (as what happens in a Grand Total, or a Subtotal), return BLANK()
3. If the current pivot context corresponds to a Blank player, again return BLANK()

This works great!  Check out the results at the top and bottom of the pivot:

When a measure returns BLANK(), that row disappears from the pivot, even the Grand Total.  Yay!

And if I add another measure that is non-blank for those rows, the Blank player comes back, but my Rank measure will display a blank cell rather than a number.

Are we done?  Depends on the intent

I said this was tricky remember?  Look what happens when I add another field to rows, like College Attended:

Neat!  Now my Rank measure treats each parent value (each College in this case) as a separate world in which to rank players.

Shaun Alexander is indeed the #1 NFL rusher from Alabama in my database.  Perhaps that’s exactly the behavior I want, and in some cases it will be.

At some point I will explore how to NOT do this – how to still display Shaun’s overall rank while nesting him under his college name.  But for now, this is enough to digest I think.

Next Football Post:  Do Not Over-Use FILTER()  >>

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.

Next Football Post:  RANK Measure Continued >>