skip to Main Content

 

image

How can we call something SuperFriendly
if it’s not sortable by State?

Sorting by State!  How did I miss that?

In a comment on Thursday’s post, Janet asked an excellent question:  what about sorting by state name?

Awesome awesome awesome.  Thanks Janet – of course the people who use this report are going to want that.

Attempt #1 – Text Measures

Given that my trick relies on sorting by HiddenSortMeasure, I somehow have to get the state name reflected in that measure.  So I need a measure that represents StateName.

I don’t think this is widely known, but measures CAN return text.  Let’s write a measure that simply returns the name of the state:

[StateNameMeasure]=

IF(COUNTROWS(VALUES(States[FullStateName]))=1,
   VALUES(States[FullStateName]),
  
BLANK()
)

If you are wondering how that formula works, by the way, I highly recommend checking out the post where I explained IF(VALUES).

If I add that measure to the pivot and sort by it, I get:

image

Well, the measure worked.  But the sort order is awful.  Why is Missouri ahead of Alabama?  A programmer will surely scold me for asking that, but seriously, I have no clue.

OK, let’s try another technique.

Attempt #2 – Add a column to the States Table

image

Getting that column into PowerPivot was actually a bit trickier than I’d like it to have been, since this was a copy/pasted table.  If this were a serious production application, I would have been using SQL as the source for this table, and I would have asked my database colleagues to add it for me.

But since this is, ahem, UFO data, I pasted a second, two-column States table into PowerPivot – StateName and AlphaOrder columns and related it to my original States table (and be sure to treat the new table as the Lookup table!)  Then I used =RELATED() to add it to my original States table.

OK, now time for an AlphaSort measure:

[AlphaSort]=

MAX(States[AlphaOrder])

And that DOES sort properly:

image

BTW, I could have used MIN(), or SUM(), or even AVERAGE() instead of MAX().  I just needed something that returns the number.

Now I just need to add it to my slicer table, and add another clause to the IF() in my original [HiddenSortMeasure], and…

image

It’s Alive!!!!

A bit trickier than sorting by the other columns, yes, but doable.

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 3 Comments
    1. Thanks David. Yep, getting a lot of requests for it. Will share it out next week. Got a few more mods to make first 🙂

  1. I needed a dynamically generated (i.e. Calc Column) version of the AlphaOrder column that you made in Excel then pasted and related. I couldn’t find any thing about this on your site… or did I miss it? After some trial and error, I came up with this formula =RANKX(ALL(States), LASTNONBLANK(States[FullStateName],1), , 0)

Leave a Comment or Question