Sorting a text column non-alphabetically without circular reference

[markmsc]

Curious if there is a creative solution to this problem that has escaped me.  I looked through some sorting threads here but didn't see this exact issue.

I have a column with a few different text values in it.  Let's call it Size.  The values are:

Extra small

Small

Medium
Large

Extra large

If I sort Size aphabetically, up or down, the two Extra values are incorrectly placed next to each other.  I always want any values that are present to be sorted in the above order.

Without thinking much about it, I tried creating a sort column using the switch function, to return a number 1-5 based on the entry in Size, and then sorting Size by that.  But of course this give a circular reference error, and is disallowed.

I guess I could create a data table in the model contaning a sort column and then join to that for sorting, but that's kind of an extensive workaround just for this.  Unfortunately there is no other existing column I could use to infer the value contained in Size.  Is there a simpler solution that I am missing?

Thank you.

[markmsc]

One other thing I tried, since I mentioned to @Irwan that I am using a matrix viz...

I made the SizeSort column, and then added it as a column to the matrix, above Size.  This sorts the columns by size correctly, because of course the first order sort is SizeSort 1-5.  I then tried Show Next Level, thinking that perhaps even if SizeSort wasn't showing, the next level (Size) would still be sorted by it.  Nope.  It sorts alpahbetically when it is not visually enclosed within SizeSort in the matrix.  For now I'm showing both SizeSort and Size in the viz, so that it sorts correctly, but I'd rather not do that.

[Irwan]

hello @markmsc 

i dont know how your data looks like so you got circular error.

But re-define then sort based on the defined value should be do the trick.

for example : in new calculated column, using if statement

- unsorted

- sorted

put the previously created column in tooltip, then sort the visual with that value

Hope this will help.

Thank you.

[markmsc]

Hi @Irwan -- Thank you for the suggestion. That is clever.  Unfortunately, it seems to be one that would only work for a chart visual; I am using (and did not mention this in my first post) a matrix viz.  Size is the main column in the matrix (let's say that the values shown for each size are a count of the inventory in the size).  So I want my columns to sort Extra Small > S > M > L > Extra Large.

As for the circular refernce, I tried making a calculed column, let's call it SizeSort:

SizeSort = SWITCH([Size],

"Extra Small", 1,

"Small", 2,

....

)

Then I set the sort column for Size in the semantic model to be SizeSort.  But of course Size can't be sorted by a column that itself refers back to Size.  

[Irwan · Accepted solution]

hello @markmsc 

matrix visual has fewer sort option.

you can tweak this sort by various way.

1. define using number as prefix

2. if the prefix looks ugly, then you can create multiple measures. You can set placement by ordering those measures in value column matrix visual.

Hope this will help.

Thank you.

[markmsc]

Thank you.  I did end up just prefixing with a number.  Not ideal, but it gets the job done for now.  I appeciate your thoughtful suggestions.