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:
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,
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.
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 !