Best in Class: A DAX Measure for Top Performers

Reading Time: 2 minutes

I’d just like to share a little technique I’ve used to solve a number of different business problems. I’ve often been asked to display the best, or worst, performing member in a set of records. For example, the highest grossing store per region, or the worst salesperson per store. But the trick is showing the name of the object, rather than the value of their performance.

Non-Numeric Measures
The values in this Matrix are far more useful than just displaying the value of the most medals won.

Thankfully, there is a fairly simple pattern for dealing with this situation, and it scales pretty well.

Simply put, the {Category Table} lists the options we wish to compare (retaining filter context), and the {Expression} is the Measure by which we want to compare them. Wrapping the TOPN function in a CONCATENATEX eliminates potential errors we may experience because TOPN can return multiple rows in the case of ties.

For a far more thorough explanation of the underlying theory and alternative example, I explain the technique in great depth on SQL Server Central.

SQL Server Central Article

I presented a talk in Los Angeles at SQL Saturday #640 on this topic, and parts of this talk are referenced in my lightning talk, “Best of Class: A DAX Measure for Top Performers”, and my “Working with Complicated Data Models in DAX” session.

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *