Why am I getting a circular dependency error?

[possible-possum]

I am updating a report I created a long time ago to be more efficient. I am trying to swap out a long nested IF statement for a SWITCH statement in a table, but I keep running into an error.

I have three columns. The purpose of the columns is to calculate how long a project has been active for, and to sort the projects into time buckets for use in a visual.

Column one calculates the days the project has been active for:

Column two sorts the project into a bucket based on column one:

And the final column sorts the bucket labels of column two so the stacked bar chart I use to visualise the data shows the values in order.

The end result looks a bit like this:

I figure that it would be easy to swap the IF statements in column three to use SWITCH instead as the values are static scalar values, but I keep running into a circluar dependency error when I go to sort column two by column three and I can't work out why! 

New measure:
Age Sort =
SWITCH(
'Projects'[Age Undelivered Bucket],
"<30 Days", 1,
"30-60 Days", 2,
"60-90 Days", 3,
"3-6 Months", 4,
"6-9 Months", 5,
"9-12 Months", 6,
"1 Year +", 7
)

[kristel_tulio]

Hi @possible-possum 

You can use SWITCH function

[Anonymous · Accepted solution]

Hi @possible-possum ,

I have created a simple sample, please refer to it to see if it helps you.

Create a column.

age sort11 =
SWITCH (
    TRUE (),
    Sheet2[Age undelivered Bucket] = "<30 Days", 1,
    Sheet2[Age undelivered Bucket] = "30-60 Days", 2,
    Sheet2[Age undelivered Bucket] = "60-90 Days", 3,
    Sheet2[Age undelivered Bucket] = "3-6 Months", 4,
    Sheet2[Age undelivered Bucket] = "6-9 Months", 5,
    Sheet2[Age undelivered Bucket] = "9-12 Months", 6,
    Sheet2[Age undelivered Bucket] = "1 Year +", 7
)

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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