Customize your sort order of a text column in the table

Tips and Hacks
#1

Currently, when sorting the column that has text (or string) data type, the order will be sorted alphabetically.

For example, you have the column with 3 distinct values: high, medium, low. If your sort descending, the order will be like below which is unexpected

priority (desc)
medium
low
high

The correct order should be:

priority (desc)
high
medium
low

Since the sort is not customizable, you could utilize our business calculation to transform your value into 1-low, 2-medium, 3-high and apply the sort afterward

priority (desc)
3-high
2-medium
1-low

The sample syntax for transforming the column using business calculation could be

case(
  when: your_model.priority == 'High', then: '3-High',
  when: your_model.priority == 'Mid', then: '2-Mid',
  when: your_model.priority == 'Low', then: '1-Low',
  else: 'others'
)
#2

This is a good tip, and we actually build the dimensions in the models to do this. But, what would be preferable would be being able to have a distinct sort order field.

Even better perhaps, having a sort key defined within the dimension definition, so it always orders correctly rather than users needing to know to use a separate sort field - a bit like what we already do in that case statement, but where the output is stored as a separate key pair.

2 Likes
#3

That’s is a very good idea @david-ri, what I have imagined could be something like

dimension priority {
    label: 'Priority'
    type: 'text'
    order_by: 'priority_index'
}

dimension priority_index {
    label: 'Priority Index'
    type: 'number'
    hidden: true
}

Of course, this is just an idea and we can actually make it better in the future. But feel free to drop any of your suggestions below

3 Likes
#4

I would also vote for this type of idea. We have a string field with > 10 unique values. Using the above “workaround” does not work as the ordering is:
1 - val1
10 - val10
11 - val11
Which is obviously incorrect also.

Many other BI tools (Data Studio, PowerBI) already allow you to do this and I think it is a key feature that is required.

#5

I guess we can do something like “01 - val1”, but yeah, the workaround is not scalable, the long-term solution is being voted and discussed here