, ,

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:



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…


It’s Alive!!!!

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

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog