Circular dependency when sorting a column by another column

[Learner22]

Dear Community,

I have a calculated column like this

interval

0-5

11-20

6-10

21-30

>30

I want to have the column interval like this

interval

0-5

6-10

11-20

21-30

>30

I use this column in a matirx, but I get the wrong order. I would like to have the cloumn in a matrix in this way

0-5   6-10  11-20  21-30  >31

I have created a second column (let's call this index) like if the interval is = 0-5 then 1, if interval = 6-10 then 2 etc. If I order the interval column by the idex column, I get the error cylic dependency.

How can I solve this problem?

Thank you very much for your help.

[Anonymous · Accepted solution]

Hi  @Learner22 ,

I created some data:

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[interval],"Index",MINX(FILTER(ALL('Table'),'Table'[interval]=EARLIER('Table'[interval])),[Index]))

3. [interval] – Sort by column – [index].

4. Connect two relations with [interval].

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Learner22]

Hi @Anonymous ,

thank you very much for your help. There ist just one problem regarding your solution. I want to have the following created table

Interval     index

0-5              1

6-10            2

11-20          3

21-30          4

>30             5

For example, in your case, the interval 11-20 (and not 6-10) has the index number 2.

How can I assign the correct index number to the intervals?

Many thanks!

[Learner22]

I was able to solve the problem. Thank you again for your help.

[tamerj1]

Hi @Learner22 

use the same dax code you used to created the column [interval] only replace the interval with integer number. For example replace "0-5" with 1 and "6-10" with 2 and so on. Then use the new column to sort by. 

[LPBI]

Landed here with the same issue, I prefer your solution rather than create another table 👏

[Learner22]

Hi @tamerj1 ,

Thank you for your reply. I want to keep the interval column, because the entries in the interval column refer to the days. Unfortunately, replacing "0-5" with 1 and so on doesn't help me.

[tamerj1]

@Learner22 

Of course you will keep it. Just create a new column