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

#### 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 12 Comments

1. Lulu says:

Hi. I am fairly new to PowerPivot. I have tried to replicate this syntax in my own table. But, what happens is I get the following error:

“The function used in this expression is not a valid function nor the name of a measure”

I have tried so hard but can’t edit the issue out!

=COUNTROWS(
FILTER(
ALL(tD_new [ID]),
[Dev](Values(tD_new [ID])) < [Dev]
)
) + 1

1. Do you have a space between your table name and your column name? It looks like you do, and that’s not legal.

tD_new [ID] –> not good
tD_new[ID] –> good

2. WH says:

I get the same error: “The function used in this expression is not a valid function nor the name of a measure”

If I remove the Values fuction, then the error goes away and I get data, but the rank obviously doesn’t work.

Here is my formula:

I've checked for spaces and no luck. Also I noticed when I move the cursor around the formulas it usually gives me a tooltip with the syntax, but when I reach the Values function it doesn't.

Any suggestions?

WH

3. Eric Hutton says:

Yup, I’m getting the same error as these people… it does not want to accept that VALUES statement, not even recognizing that there is function being input at that point.

4. Eric Hutton says:

DOH! If you are getting the above errors it is because you are probably putting this in as a formula in a PowerPivot table – this is a measure, to be used on a PowerPivot Pivot Table.

5. Eric Hutton says:

This is what worked for me as a table formula

=(countrows(filter(all(Registry),Registry[Average Monthly Output]>EARLIER(Registry[Average Monthly Output]))))

Now though I’m trying to come up with a decile ranking, and that is proving more of a challenge – if you use to get an inverse of the ranking, and you have a bunch of blanks, or zeros in the bottom of the data, your ranking will not start from zero.

6. Eric Hutton says:

Ok, that wasn’t so tough…

=countrows(filter(filter(all(Registry),Registry[Average Monthly Output]0))

then I can use a separate column to normalize that ranking and turn it into deciles, quintiles, whatever

1. Eric Hutton says:

Sorry.. some kind of cut and paste error there – to get a ranking where the biggest number has the highest rank value, and you start with a zero value for all zeroes, this worked for me:

=countrows(filter(filter(all(Registry),Registry[Average Monthly Output]0))

1. Searching on the internet for a DAX formula to rank things – and came across my own old solution… but it still has that weird cut and paste error – here is the fixed version:

=countrows(filter(filter(all(Registry),Registry[Average Monthly Output] > 0, Registry[Average Monthly Output] < = EARLIER(Registry[Average Monthly Output]))))

2. Marco Russo says:

Nice job Eric, you used the same approach we described in our book for calculating the ranking in a calculated column. As you noted, when you have a measure you need to use VALUES instead of EARLIER in order to catch the “current” value to be compared with – that in case of a PivotTable is the selection correspondent to the evaluated cell, instead of the current row in the PowerPivot table.

7. eric hilton says:

I noticed one key thing that might cause the above error… you have to use a calculated measure as the value being compared, you cannot a column.

I am still getting the blank row, always the first rank, only sometimes, when using this. Not sure of the problem yet.

8. Brian Butler says:

Awesome solution to a tricky problem… and when ranking for the values that a better when lower, a simple switch from “” does the trick. I’ve ranked several catgeories KPI individually, and am unable to sort out how to take those ranks to an overall rating.