Rank continued – dealing with unknown members

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.