Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
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?
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
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
Hope this helps
Regards