Forum Discussion

Learner22's avatar
Learner22
Helper I
3 years ago
Solved

Circular dependency when sorting a column by another column

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's avatar
    Anonymous
    3 years ago

    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

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

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

    • Learner22's avatar
      Learner22
      Helper I
      3 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Learner22
      Helper I
      3 years ago

      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's avatar
        Learner22
        Helper I
        3 years ago

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