cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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,

Regards

 

2 REPLIES 2
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.

3.png

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.

4.png

 

 

Best Regards

Maggie

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 !