Sort Order Question

[cheid1977]

I created a formula to put shipping weights into weight ranges (0 - 5, 10 - 20, etc.). The problem I am having is that the weight ranges are not in the correct order from smallest to largest.  For example after 10 - 20 it jumps to 100 - 150, then to 1000 - 2000. Is there anyway to put these in order?

[cheid1977]

I created a second column. When I went to sort per your instructions I got error message "This column can't be sorted by a column that is alreadsy sorted, directly or indirectly, by this column".  Is this anyway related to using DirectQuery?

[AlbertoFerrari]

Likely, you based the second column on the first one. Something like: "IF RANGE IS 10-20 THEN 1 ELSE ...".

This makes the sorter column (your second one) dependant on the sorted one (the calculated range). In such a case, you cannot use the sort-by-column feature.

You need to remove the dependency in one of two ways:

- you repeat the full logic in the sorter, so to avoid the dependency

- you reverse the dependency by first creating the sorter, and then assign a description to the sorted column based on the sorter

Either ways, the dependency is removed or switched in a direction that DAX likes more.

What I prefer is to first compute the sorter, then the sorted. Something like this:

Sorter = 
SWITCH (
    TRUE (),
    YourTable[Weight] < 100, 1,
    YourTable[Weight] < 1000, 2,
    YourTable[Weight] < 10000, 3
)

Sorted =
SWITCH ( 
    YourTable[Sorter],
    1, "LESS THAN 100",
    2, "100-1000",
    3, "1000-10000".
    "VERY HEAVY"
)
 

In this scenario, Sorted can now be sorted by Sorter.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[TomMartens]

Hey,

the problem you face is due to the fact, that the string columns are ordered alphabetically by default. To solve this issue you have to create a second column of data type whole number (this column can be hidden from report view). Assign a proper value to this column.

In a product table your columns than may look like this

Product | Weight Range | SortOrder

Product A | 10 - 20 | 1

Product B | > 100 | 3

Product C | 21 - 100 | 2

Product D | 10 - 20 | 1

Please be aware, that the same value for the SortOrder column has to be assigned for the same value of the Weight Range column.

In the model pane

• switch to the Modeling menu
• mark the column weight range and 
• select the newly created colum from the Sort By Column submenu

Hope this helps

Regards