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

[mdlm778]

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,

Regards

[v-juanli-msft]

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

Maggie

[mdlm778]

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 !