DAX – Top-Selling Product using FIRSTNONBLANK()

First of all, apologies for being so late.  I feel like a total zombie, we got to our house in Ohio 22 hours after we arrived at the airport in Tel Aviv.

Second, how cool is this pivot???

Yes, This is a Pivot with Two Measures!

In last Thursday’s post I showed one way to display the top-selling date, or “date on which we had maximum sales” as a measure in a pivot:

And the formula for [Date of Max Unit Sales] was:

=IF(ISBLANK([Sales Units on Max Day]),BLANK(),
   CALCULATE(FIRSTDATE(Calendar[Date]),
      FILTER(VALUES(Calendar[Date]),
         [Sales Units]=
         CALCULATE([Sales Units on Max Day],
            VALUES(Calendar[Date])
         )
      )
   )
)

But What if You’re Looking for the Best Selling Product?

In the formula above, note the highlighted use of the FIRSTDATE function.  That works great when I am looking for a top-selling date, as I was in the last post.

But what if I want to find something that isn’t a date?  What if I want to know something different, like the top-selling product?

Well, if I want to return a numeric/integer product ID, I could always substitute MIN or MAX for FIRSTDATE.  But what if it’s the product name I want to see, which is far more useful anyway?  There’s no MIN/MAX style function for text, sadly, so I have to get creative.

Use VALUES() Instead?

Well the VALUES() function will return text as long as my FILTER function returns only a single unique value.  If there are two or more products tied for the top-selling title, however, simply using the VALUES function will return an error.  More details here on that here.

Detecting the case where the FILTER returns more than one row seems awkward however, at least to my jetlagged brain.  So I’m gonna go a different route and use FIRSTNONBLANK() – a function I have been meaning to explore for awhile now anyway.

FIRSTNONBLANK (and LASTNONBLANK)

These functions strike me as having a million uses – the sort of thing where I have yet to grasp their full potential.  For them to make their “debut” here 2.5 years after the blog’s inception seems inappropriate, but hey, better late than never.

The syntax of “FNB” is quite simple:

FIRSTNONBLANK(Column, Measure Expression)

It’s actually a lot like SUMX in that it’s a “looping” function – it loops through all the values of Column, evaluates Expression each time, and returns the first value of Column for which Expression returns something other than Blank.

Let’s cut to the chase and try it out on Products:

[Name of Best Selling Product] =

FIRSTNONBLANK(Products[Name],
   IF([Sales Units] =
      CALCULATE(
         [Max Single Product Sales Units],
         VALUES(Products[Name])
      ),
      1,
      BLANK()
   )
)

Where everything in Green is the Measure Expression.  In this case I used an IF() that returns 1 when the current product’s [Sales Units] value is equal to the value of [Sales Units] for the best-selling product.  The latter is determined by my [Max Single Product Sales Units] measure, which is a MAXX measure just like how I did it in the previous post:

[Max Single Product Sales Units] =

MAXX(VALUES(Products[Name]), [Sales Units])

This all works great.  But just like in the last post, I have to add an IF() “wrapper” to prevent the measure from returning product names in cases where we sold nothing:

IF(ISBLANK([Sales Units]), BLANK(),
   FIRSTNONBLANK(DimProduct[EnglishProductName], IF([Sales
   Units] = CALCULATE([Max Single Product Sales Units], VALUES
   (DimProduct[EnglishProductName])), 1, BLANK()))
)

Where the orange text is the new wrapper and the grey is the original measure formula from above.

Flexible, Portable!

Like all good PowerPivot measures, this formula works in a lot of different contexts.  Best-selling product per year-month is cool, but we can also see it per city by changing the field on rows:

Just change the field on rows, formula still works!

What about ties?

OK, what about the case where two or more products tie for best selling?

Hmm, well, I must say I don’t have a perfect solution for this.  Here’s one partial solution though:

I created a second version of my original measure, but replaced FIRSTNONBLANK with LASTNONBLANK:

[Name of Best Selling Product LNB Version]=

IF(ISBLANK([Sales Units]), BLANK(),LASTNONBLANK(DimProduct[EnglishProductName], IF([Sales Units] = CALCULATE([Max Single Product Sales Units], VALUES(DimProduct[EnglishProductName])), 1, BLANK())))

And then a third measure which compares the original to the new version:

[Best Selling Product Ties Detected] =

IF([Best Selling Product]=[Best Selling Product LNB Version],
   [Best Selling Product],
   “Multiple Products”
)

In theory that should work – if there’s a tie, the first and last products will be different.  But for the life of me, I cannot get this sample data set to yield a result of “Multiple Products” anywhere, no matter what I try.  So I am suspicious that maybe the FNB and LNB functions don’t respect “first” and “last” quite so faithfully.  Let me know if your results differ.

There are other problems here too of course.  It would be nice to know both product names in the event of a tie, in which case the measure could concatenate their names rather than returning “Multiple Products.”  But if there were three products tied, I don’t even know how to get the name of the “middle” product.  Perhaps in PowerPivot V2 I’ll discover a way.

More Efficient Way in PowerPivot V2?

Speaking of V2, Gerhard posted a comment on the last post that is worth calling out:

DAX – Finding the Top-Selling Date, Product, etc.

But given my limited experience with V2 to date, that’s over my head for the moment

Your Uses of FNB/LNB

I’m curious – are you using FIRSTNONBLANK() and LASTNONBLANK() today?  And if so, I bet you are using it in a very different manner.  Send me an email or leave a comment, I would very much like to know where and how you are using these functions.