Bringing a Treasure Trove Back into the Light

Power BI Desktop Top N Value Slicer Dashboard

Guess how many articles are here on PowerPivotPro.com?  Go ahead and think of a number, I’ll wait.

The answer, at time of writing, is 923.  Rob alone has published 715 articles!  And these date all the way back to 2009.

A lot of these articles are “old,” but folks, the DAX engine is still 99% the same today in Power BI (and Excel 2016) as it was when it first “hit the shelves” in Spring 2010.

The motivation behind this “Reanimator” series, then, is twofold:

  1. Help newer converts/readers rediscover some of the most-awesome techniques previously covered here (without being so lazy as re-posting them in their original form)
  2. “Refresh” those techniques for the brave new world of Power BI (since the vast majority of old articles were written when we only had Power Pivot)

What better way to do that than to re-create those workbooks in Power BI Desktop and embed the report directly…Within. This. Post! Smile

A New Age of Self-Service BI Users

I’ve been fortunate enough to be given the honor of sharing with you, our community, all these wonderful posts written by many of our in-house industry experts. Updated in all their glory into the wonderful world of Power BI. Now you can click, slice, interact, touch (…dirty), and drill (dirtier!) with these reports to your hearts desire. Just as the BI gods intended them to be! My hope is that these updates will instill these tools to the growing number self-service BI users just getting into the field and who want to do AWESOME things with their reports.

Highlights From The Original Post(s)

So this update is actually a continuation of not just one…but TWO posts written by Rob in the distance past of 2012 (in technology years that’s basically forever). The two original posts were:

Dynamic TopN Reports Using PowerPivot V2!

Dynamic TopN Reports via Slicers, Part 2

Power BI Desktop Top N Value Slicer Dashboard

Rob demos some pretty ingenious techniques using his (now prolific) disconnected slicers technique to not only control the Top N Number you’d like to see on charts or graphs, but also the Value that you want to see that Top N Number ranked on. I’ve used it in MANY reports I’ve made over the years, always impressing the customers who used them.

Now I don’t want to give too much away in this post, instead directing you back to the walkthrough via the links above. I’m just here to whet your appetite enough with some fancy Power BI Reports, and if you want to learn the DAX code, hop into Rob’s posts.

This “Picture” Below is an Interactive Power BI!

Isn’t Something Missing?

Some of our more avid blog readers may be thinking “wasn’t there a THIRD post about TopN filtering?”. Yes, in fact there was. It was written by guest contributor Colin Banfield and is called Dynamic TopN Reports via Slicers, Part 3. It’s a fantastic post which covers ways to add BottomN metrics, Month/Year slicers, and more. I chose not to use that workbook since I wanted to capture the core story from the original posts written by Rob. If you’re inclined however, I recommend reading all three as they will add real value to your DAX tool belt. Until next time P3 Nation!

Download the Files!

Download the PBIX files

X

Get Your Files

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro. His passion is collaborating with individuals and organizations to help them understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around Business Intelligence, Reporting, & Design. 

This Post Has 17 Comments

  1. This is great! I am in the process of trying to port an Excel data model over to Power BI and this has given me some very good ideas for layout and navigation (especially the TopN slicer!).. Just a quick question: did you create this model from scratch or did you import an Excel model? Or does it even matter? Thanks Reid!

    1. Hi Jeff,

      Glad you could use some design techniques from this one! One of the primary benefits of this series, importing the data model from Rob’s previous Excel workbooks. Saves hours! Many many more to come. ????

  2. One of the challenges I’ve found with Rob’s excellent idea is that the TopN measure only works for a single attribute (in the attached files for example, you’ve tied it to Full Name). A small thing, but it does mean you need a separate TopN measure for every attribute that someone might want to rank, making it less helpful in self-service situations. (But still nice on polished pre-built reports that you’re presenting to someone else.)

    Another factor is that Power BI now has a built-in TopN function, that allows you to filter a visual the same way (you select the attribute to filter on, you select the measure to filter by, and you can type in how many you want): https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/#topN

    The main disadvantage of Power BI’s TopN function is that the N value can’t be set by slicer – it’s hidden away in the filters. Not inaccessible, but certainly not as slick. Still, the disconnected table, plus having to have a measure for each attribute you want to rank, might be a lot to add to your model for that slickness. Plus I don’t know how much end-users need the ability to switch quickly from Top 5 to Top 10 (say), when Top 10 includes the Top 5. It likely depends on the end-user.

    I mention this not to be critical, but more so that readers know the pros & cons of this method vs. functionality that didn’t exist when Rob wrote his original posts. I’m excited that you’re revisiting the classic posts and adding an update. There’s so much of value in your archive!

    1. Hi Leonard,

      Thanks for the thought provoking response. Yes we definitely have come a long way in features in Power BI since the original article. To your first comment you can actually add another disconnected table to allow switching between what is being ranked on. (E.g. Revenue, Cost, Order Count, etc…) much the same way we change the categoey we’re ranking currently. Adds complexity yes, but in theory you could build out something to rank a variety of categories against a variety of values dynamically. Handy in niche scenarios!

      1. I hadn’t thought of that! Very wise!

        So, in your attached PBIX file (which downloaded as a zip file btw), I can change the measure Customer Ranks by Selections as follows:

        Customer Rank By Selections = IF(HASONEFILTER(Customer[Full Name]), RANKX(ALL(Customer[Full Name]), [Selected Top N Value], ,0), IF(HASONEFILTER(Customer[Last Name]), RANKX(ALL(Customer[Last Name]), [Selected Top N Value], ,0), IF(HASONEFILTER(Products[English Product Name]), RANKX(ALL(Products[English Product Name]), [Selected Top N Value], ,0))))

        Then, no matter if my visual is ranking Customer Full Name, Customer Last Name, or English Product Name (the 3 I’ve specified in my formula), the Top N function continues to work. Still hard-coded attribute names to some extent, but it’s better than creating 3 measures.

        1. Hi Leonard,

          You’re correct that you could write a single DAX measure for this. However I try not to make a single query too long if I don’t need to, makes it more visually cluttered and harder to troubleshoot later if you need to change something. I like to think of DAX Measures as loge blocks, where I created each separately and then referenced in the single switch query. It doesn’t have a performance impact when ran this way, and keeps the final query simple and easy to read. Rob adheres to this philosophy too. Neither is better, more of a personal preference since they both take the same amount of time to run. 🙂

          1. I agree. It does make sense to have helper measures. (I was being quick – I didn’t even format, which I realized I should have done right after I posted it).

            Note that I didn’t use SWITCH in the end because I’m switching based on the filter. I thought it would be preferable to automatically detect what the measure was being ranked by, rather than forcing the end-user to have to pick from a 3rd slicer. (This gets more complicated if I’m filtering & ranking > but I won’t sidetrack your post any more than I already have.)

            Thanks for your help (and your idea!)

    1. That’s a great question Narayana. With the Import Data Model from Excel feature most of the heavy lifting for the model was already done, I only spent a few hours building out the reporting page you see above. 🙂

  3. Leonard back to your statement, comments and suggestions for DAX measures are always welcomed! We love seeing how other people write these things. With code there is not always a “Best” way so it’s great to see other people’s methods. Thanks for sharing!

  4. Concerning the TOP N, I would like to filter (to read) the last n months, n years directly in the query itself.

    Do you have a solution ?

    Best Regards,

  5. Are you using something other than the standard Table visualization in Power BI desktop? I downloaded the above PBIX file and can not get the ‘Customer Rank by Selection’ to work when duplicating your bottom left table within the downloaded PBI report. It obviously works with the “table visualization” that you are using but I am wondering if Microsoft changed something in one of the recent desktop updates to make this not work as it should.

    Any advise in fixing this would be greatly appreciated! (And thanks for the above info)

Leave a Comment or Question