“…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()  >>