topic Circular dependency when sorting a column by another column in DAX Commands and Tips

Circular dependency when sorting a column by another column

Learner22 — Wed, 14 Dec 2022 14:05:25 GMT

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.

Re: Circular dependency when sorting a column by another column

tamerj1 — Wed, 14 Dec 2022 14:59:00 GMT

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.

Re: Circular dependency when sorting a column by another column

Learner22 — Wed, 14 Dec 2022 15:23:34 GMT

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.

Re: Circular dependency when sorting a column by another column

tamerj1 — Wed, 14 Dec 2022 15:51:51 GMT

Learner22

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

Re: Circular dependency when sorting a column by another column

Anonymous — Thu, 15 Dec 2022 06:56:42 GMT

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

Re: Circular dependency when sorting a column by another column

Learner22 — Thu, 15 Dec 2022 07:51:27 GMT

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!

Re: Circular dependency when sorting a column by another column

Learner22 — Thu, 15 Dec 2022 07:57:40 GMT

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

Re: Circular dependency when sorting a column by another column

Anonymous — Thu, 01 May 2025 20:12:51 GMT

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