sort a non-unique column by another one

[eshetb]

hey,

i'm trying to sort one column which has duplicate values by another one.

assume this example, where i want to have "status_category" sorted by "rank":

status	status category	rank
new	in progress	10
first review	in progress	20
second review	in progress	30
rejected	closed	40
on hold	hold	50
closed	closed	60
canceled	closed	70

the problem is that one category value cannot have more than one rank value. (getting a can't sort error)
so i created another column, taking the min rank value for each category:

status	status_category	rank	min_rank
new	in progress	10	10
first review	in progress	20	10
second review	in progress	30	10
rejected	closed	40	40
on hold	hold	50	50
closed	closed	60	60
canceled	closed	70	60

using this:

min_rank = 
VAR currentCategory = table[status_category]
RETURN
    CALCULATE (
        MIN ( table[rank] ),
        FILTER ( ALL ( table), table[status_category] = currentCategory)
    )

so now there's only one possible rank for each category value.
however, wheb ttying the sort by option i get a new error: "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column".
am i to understand that it's not possible to sort a column by another one that's calcaulated by it?

can anyone find a solution to either of the sort errors?

[v-piga-msft · Accepted solution]

Hi @eshetb,

---

m i to understand that it's not possible to sort a column by another one that's calcaulated by it?

---

Yes, you should be right.

I think you could create the condition column in Query Editor and sort by that column.

For another option, you could have a try with create another table with formula like below.

Table 2 = {(
"in progress",1),
("closed",2),
("hold",3)}

Then sort category column by index column and create the relationship with the original table.

Here is an example:

Hope this can make sense of you.

Best Regards,

Cherry