Showing results for 
Search instead for 
Did you mean: 
mdlm778 Frequent Visitor
Frequent Visitor

Sort a column with another one but in calculated table: error

Hi everybody,


I have a new question for you, regarding the sort of a column according another one. I'd like to use it because I have an histogram with classification (for instance, [-15;0[,[0;15[, …) and I want the groups to be displayed on the chart in the "numeric" order and not in the alphabetical order. Therefore, I add a table with match between the groups and a numeric order.


My problem looks like Sort-by-another-column-error at the end of the topic, but no solution was found.


Let me explain in more details.


I have a table (let's say DataTable) like this:

ID        MyDurationA_Cat          MyDurationB_Cat

ABC              [0;15[                               [15;30[

DEF               [-15;0[                             [-15;0[


I have a table (let's say TimeTable) like this:

MyDuration       Order

[-15;0[                   1

[0;15[                    2

[15;30[                  3


DataTable has been created by a calculation from AllDataTable. Therefore, DataTable does not appear in the query editor.

TimeTable has been created with the query editor.


I have made a relationship (active) between the 2 tables:


DataTable.MyDurationA_Cat = TimeTable.MyDuration


I have made a second relationship (not active) between the 2 tables:

DataTable.MyDurationB_Cat = TimeTable.MyDuration


I have 2 calculated columns in my DataTable (both works):


MyDurationA_Cat_sorted = RELATED(TimeTable [Order])

MyDurationB_Cat_sorted = LOOKUPVALUE(TimeTable [Order]; TimeTable [MyDuration]; DataTable [MyDurationB_Cat])


I have sorted the column MyDurationA_Cat with the column MyDurationA_Cat_sorted: it's OK


I have sorted the column MyDurationB_Cat with the column MyDurationB_Cat_sorted: it's KO

I received an error message that I will try to translate: "This column cannot be sorted according a column already sorted, directly or indirectly, by this column". So I'm not sure but I guess they say that I'm trying to sort MyDurationB_Cat with MyDurationB_Cat_sorted but for them, MyDurationB_Cat_sorted is sorted with MyDurationB_Cat, so we have like a circular reference.


Could you help me solving my problem ?


Thanks in advance,



Community Support Team
Community Support Team

Re: Sort a column with another one but in calculated table: error

Hi @mdlm778

I test with your dataset and formula, it finally turns out to this result.


When I sort the column MyDurationB_Cat with the column MyDurationB_Cat_sorted, it shows the same error as you. As I far as I know, the error occurs because the column MyDurationB_Cat_sorted is calculated with the column MyDurationB_Cat.

However, I can sort other columns except MyDurationB_Cat in DataTable with the column MyDurationB_Cat_sorted. Thus, I can sort the column MyDurationB_Cat_sorted indirectly in report view.




Best Regards


mdlm778 Frequent Visitor
Frequent Visitor

Re: Sort a column with another one but in calculated table: error

Hi @v-juanli-msft


Thanks for your reply.


I think I get what you meant. I succeed to reproduce your tip in the table in the query editor by sorting the ID field with the field MyDurationB_Cat_sorted. But I didn't succeed when I go back in the design view, neither on a listing (if I sort by ID, it is still the alphabetical order of the ID) nor on the histogram (the bars are still ordered according the alphabetical order of the groups in MyDurationB_Cat as I let default sort for MyDurationB_Cat and as there is no other field in my histogram).


I have tried something else. It works whereas it is really not optimal. I divided ma table TimeTable into 2 tables: TimeTableForDurationA, TimeTableForDurationB. The 2 tables are duplicate. If one day I will have other field to be sorted on a chart, I will have to triplicate my table...


If you have any idea for the histogram, thanks in adavance.

Have a nice day !