Adding State Alpha Sort to the Sort By Slicer Trick

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:

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

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:

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…

It’s Alive!!!!

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