Storing and numerically ordering a values based on lens data - numeric and text values

[maracles]

Hi,

I have a database of lenses and one of their characteristics is 'Focal Length'.

This is measured in mm and the vast majority of values are simply whole numbers. My issue is with zoom lenses which have a variable focal length stored as a text value e.g. 50-100mm.

First question; from a data point of view how should this be stored. If it was single focal lengths I would simply store as whole number, but in what way would I transform my column to make this data most usable? 

Second question; I'm trying to make my tables human readable so ideally if someone wants to produce a table of lenses the zoom's should also be ordered. For example:

If I had the following lenses I would want them ordered as below:

10-50mm
30-70mm
100-200mm

If I order by text however I would get:

10-50mm
100-200mm
30-70mm

And taking this another step up in complexity, if I had a table listing a manufacturer's lenses that produced prime AND zoom lenses I would want them to be show as follows:

- Primes first 
- Zooms second

Example:

Zeiss
   12mm
   16mm 
   24mm

   10-50mm
   30-70mm
   100-200mm

Any help would be very much appreciated. 

[Phil_Seamark]

Hi @maracles

Why not add some additional columns to your data table.  These can be added using the Query Editor or using DAX.  Split the text up so you have a column that shows the lower bound value as a number (perhaps one for the upper bound).

You can then configure your data model sort your original text column by one of the new columns

[maracles]

Thanks @Phil_Seamark, I had thought about the lower and upper bounds, how would I then tie the original text column to my sort order. 

I had seen a similar suggestion elsewhere but didn't completly see how I could combine them. 

[Phil_Seamark · Accepted solution]

Hi @maracles

Just use the original text based column.  You can use the "sort by other column" to tell the engine to use the new, numeric column to control the sort order.

[maracles]

Thanks @Phil_Seamark, just got around to putting that into practice and it (mostly) works nicely. Just some clarification for anyone else reading this, you need to select 'Sort By Column' in the Grid view. Select the column you want to sort, then go to 'Modelling > Sort By Column' and select from there.