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:
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:
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…
A bit trickier than sorting by the other columns, yes, but doable.