Forum Discussion

eshetb's avatar
eshetb
Frequent Visitor
8 years ago
Solved

sort a non-unique column by another one

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":

 

statusstatus categoryrank
newin progress10
first reviewin progress20
second reviewin progress30
rejectedclosed40
on holdhold50
closedclosed60
canceledclosed70

 

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:

 

statusstatus_categoryrankmin_rank
newin progress1010
first reviewin progress2010
second reviewin progress3010
rejectedclosed4040
on holdhold5050
closedclosed6060
canceledclosed7060


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's avatar
    v-piga-msft
    8 years ago

    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

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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