skip to Main Content

DIVIDE's 3rd argument set to a text value: You probably want it to not impact your RANKX measures. Here's How.

This is What I Want (What I Really Really Want):
Zeroes to Rank Poorly. Divide by Zero Errors to Display “N/A.” And “N/A” to NOT participate in my ranking.

RANKX: the Mother of Many Blog Posts

Ya know, we could probably write articles about RANKX for an entire year.  There are just SO many subtleties to it, and SO many different ways you might want to rank things.  Endless source of blog fodder.

For example, let me share a hiccup I had to fix recently for our No Checkdowns site.

DIVIDE()’s Awesome Third Argument

You probably are already aware, but just in case, Part ONE:  you don’t need to do IFERROR() to protect yourself against division by zero in DAX.  Just use the DIVIDE function, and you’ll get blank cells instead of errors whenever you have a division by zero.

You probably are already aware, but just in case, Part TWO:  DIVIDE offers an optional third argument which allows you to return a result other than BLANK() in case of div by zero.  Usually I leave it empty and just roll with the blank result.  But sometimes, I’ve used 1 or 0 as the optional third argument – because in some fairly rare circumstances, you want to treat a div by zero as 0% or 100%.

You probably are already aware, but just in case, Part THREE:  that third argument can also take a TEXT value!  So, instead of returning an error, or a blank, or a 1/0…  you can return something like “N/A.”

Consumers Love Them Some “N/A”

A blank cell in a report is sometimes a source of confusion for those human beings consuming our work. “What does a blank cell mean,” they ask.  “It’s a division by zero,” we reply.  “Wut,” they then ask.  “Trust me,” we say, “you don’t want to see the alternative.”  “But I don’t trust you, and now I don’t trust this whole report,” is what they sometimes say next – whether under their breath or out loud.

But “N/A” is a lovely value to display.  It raises far fewer eyebrows.  “Oh, it says our Profit Margin % for electric blankets sold in Cancun is “N/A” – I get it, we’ve never sold that product there.”  No convo required.

So Let’s Use It!

It doesn’t get any more “consumerish” than a report displaying sports data, so I’ve chosen to use “N/A” as the third argument to a lot of my DIVIDE() measures.

For example:

   Pass Accuracy % = DIVIDE([Accurate Passes],[Pass Att Qualified],”N/A”)

and then, from that, I derive another measure…

   Accuracy % Left Seam = CALCULATE([Pass Accuracy %],DimZone[Seam]=”Left Seam”)

And it looks great:

Start out with Power BI DIVIDE Measure Returning N/A Instead of Blank

“N/A” Looks Great – But the fact that it sorts to the top hints at future troubles.
(In this case, N/A “means” that this player has not even attempted
such a pass, so there can be no accuracy number)

Now, Release the RANKX!

Let’s write the most straighforward version…

   Bad Rank v1 = RANKX(ALL(DimQB),[Accuracy % Left Seam],,,Skip)

…and compare it side-by-side with what I WANT to see…

Power BI RANKX Measures with Text-Returning DIVIDE measures: Not What We Want (Yet)

I don’t want N/A’s to rank first.  AND I don’t want them “demoting” my TRUE Top-Ranked Items to 4.

First:  Let’s Stop Ranking the N/A’s

OK, no problem!  Let me just wrap that RANKX in an IF(ISNUMBER()) test…

   Bad Rank v2 = IF(ISNUMBER([Accuracy % Left Seam]),
                   
RANKX(ALL(DimQB),[Accuracy % Left Seam],,,Skip)
                
,BLANK())

Now, we could have returned something other than BLANK() for the else of the IF.  Maybe…  COUNTROWS(DimQB) so that they always rank last?  But in this case I just don’t even want them to get a rank, and a blank cell WILL be understood by the consumer if it’s sitting next to an “N/A.”

So anyway, let’s see what that IF-ISNUMBER-BLANK approach gives us…

Power BI RANKX Measures with Text-Returning DIVIDE measures: Step 1

Fixed the First Problem, But…  Still Need to Fix the “Demotion” Problem

Fixing the Demotion Problem

This is trickier, since our IF test does NOT get triggered on the “Brissett, J” row, for example, and that IS a row where we need to return a different result (1, rather than 4).

In order to do this, we need to take the N/A’s out of the population that RANKX looks at.  Which happens in that first argument, where we’re currently passing ALL(DimQB).

Time for our good friend, FILTER()…

   Bad Rank v3 = IF(ISNUMBER([Accuracy % Left Seam]),RANKX(FILTER(ALL(DimQB),ISNUMBER([Accuracy % Left Seam])),[Accuracy % Left Seam],,,Skip),BLANK())

   Bad Rank v3 = IF(ISNUMBER([Accuracy % Left Seam]),
                    RANKX(FILTER(ALL(DimQB),ISNUMBER([Accuracy % Left Seam])),
                          [Accuracy % Left Seam],,,Skip
                        
)
                 ,BLANK())

The highlighted portion basically says “yeah, START OUT with every row of DimQB, but then remove rows with a non-numerical value for [Accuracy % Left Seam] before you start your ranking work.”

What does this yield?

Power BI RANKX Measures with Text-Returning DIVIDE measures: Step 2

It Matches My “Rank That Works” Column Perfectly – so Why Am I Still Calling It “Bad Rank v3?”

Sneaky Remaining Problem:  Try Filtering the Visual a Bit

Let’s say I want to just see the QB’s who went pro within the past five years, so I slap a filter on the table visual…

Power BI RANKX Measures with Text-Returning DIVIDE measures: Step 3

OK…  NOW my “Bad Rank v3” is Returning Different Results than “What I Want.”
(Of course…  sometimes this IS what I would want.  But not in this case.)

Look at Marcus Mariota.  Bad Rank v3 says he’s 6th-best. But there are only two QB’s ranked above him in this report!  Shouldn’t he be third-ranked?

Yeah, trouble is…  Bad Rank v3 is still looking at ALL of the rows of the DimQB table behind the scenes!  Including “Brissett, J.” who went pro MORE than five years ago, and is not showing up in our table anymore (thanks to the filter).

OK, easy fix…  replace ALL with ALLSELECTED.

   Ranks that Works = IF(ISNUMBER([Accuracy % Left Seam]),
                         RANKX(FILTER(ALLSELECTED(DimQB),ISNUMBER([Accuracy % Left Seam])),
                               [Accuracy % Left Seam],,,Skip
                             
)
                      ,BLANK())

Remove the “bad” measures, replicate this pattern a few times, format it extensively…  and… Voila!

Power BI Formatted and Conditionally Formatted Table of DVIDE and RANKX Measures for the NFL Masses

A Beautiful Ranking Exercise If I’ve Ever Seen One
(Click Image to Visit an Interactive Version of the Report on NoCheckdowns.com)

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 2 Comments
  1. @Rob, I love this pattern! I recently had to build something similar…we didn’t want to include a district in a rank if the calculation didn’t apply to them.

    Here’s a way to optimize the measure. Think holistically for a moment.

    For a individual QB, calculate the measure. If it’s not a number, return blank. (Single calculation tally = 1)
    Otherwise, calculated the measure again for all QBs. (All calculation tally = 1)
    Filter out the non-number results.
    Use the filtered table for RANKX()…perform the same calculation for the rank. (All Calculation tally = 2)
    Return the rank.

    You’re calculating the measure for all QBs twice. If this is an expensive measure, performance will likely be slow.
    We can’t get around calculating the measure for a single QB, that’s needed to display a blank… but its performance is likely a non-issue. Let’s focus on only calculating the measure for all QBs once and only once.

    Here’s an optimized version of your measure. Note the judicious use of variables. This improves readability, performance, and aids in troubleshooting!
    If you’re stuck, just return the variables in sequence to see where the calculation breaks down.

    This works because you can use a variable to store a TABLE, and pass a variable table into functions like FILTER(), SUMX(), RANKX(), ADDCOLUMNS(), etc.

    Rank that Works (Optimized) =
    VAR base_QB =
    // start with the basic DimQB table, preserving the filters that we need to
    ALLSELECTED(DimQB)
    VAR add_Accuracy =
    // adds a column calculating [Accuracy % Left Seam] for each row
    // note that we only calculate this value ONCE for each QB
    ADDCOLUMNS(
    base_QB
    ,”Accuracy”, [Accuracy % Left Seam]
    )
    VAR Filtered_QB =
    // removes the N/A values
    FILTER(
    add_Accuracy
    ,ISNUMBER([Accuracy])
    )
    VAR Ranked_QB =
    // performs the rank based on the [Accuracy] column we created earlier
    // we don’t have to calculate the value again!
    RANKX(
    Filtered_QB
    ,[Accuracy]
    ,
    ,
    ,SKIP
    )
    VAR Result =
    // return blank if the measure doesn’t return a number
    IF(
    ISNUMBER([Accuracy % Left Seam])
    ,Ranked_QB
    ,BLANK()
    )
    RETURN
    Result

    Hope this helps,

    ~ Chris H

Leave a Comment or Question