Circular dependency error using switch

[Clint]

Hello,

I have a column chart that tracks the number of projects by what stage gate they are in.  This is a field in my projects table called "CurrentStageGate" and it is not a calculated column.  The chart uses this field as it's X axis and shows  count of projects in each StageGate.  The problem is, we want to sort the Stage gates in a certain order.  I created a calculated column in the same Projects table using switch to assign a numbe to each stage gate.  When I select the field "CurrentStageGate in the Projects table in the table/field list and choose sort column by >C_StageGateOrder (the calculated column), I receive the following circular error.

I don't really understand what this error is telling me since there is only Projects table and one CurrentStageGate field in my data model.  And the only caculated column is the one doing the sort order.

The DAX for the Stage Gate sort order calculated column:

C_Stage Gate Order =

SWITCH(TRUE(),

Projects[CurrentStageGate]="Concept",1,

Projects[CurrentStageGate]="Design",2,

Projects[CurrentStageGate]="Build",3,

Projects[CurrentStageGate]="Model",4,

Projects[CurrentStageGate]="RTS",5,

Projects[CurrentStageGate]="Ramp",6,

Projects[CurrentStageGate]="Sustaining",7

)

[richbenmintz · Accepted solution]

hi @Clint,

In Power Query you can create a custom column to use as your order by field

if [CurrentStageGate] = "Concept" then 1 else if [CurrentStageGate] = "Design" then 2 else if [CurrentStageGate] = "Build" then 3 else if [CurrentStageGate] = "Model" then 4 else if [CurrentStageGate] = "RTS" then 5 else if [CurrentStageGate] = "Ramp" then 6 else if [CurrentStageGate] = "Sustaining" then 7 else -1

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Clint]

Thanks Richard, I will do this in Power Query.  Much appreciated.

[MattAllington]

It may work in Excel, but not in PBI.  I can't be precice as I don't know the techincal reason.  What I know is that once you sort a column in PBI, both of those columns are included in the filter context.  My guess is that the calcuated column is thererfore refering to itself (it needs the first column to work out the sort value, but then the sort value is somehow tied to the filter context (and possibly row context) of the first column, hence the circular reference.

[Clint]

@MattAllington  - Thanks for the insight. I've done this kind of thing before in some of my BI reports but now I know to pay more attention to th filter context if I need to do something similar in the future.

[MattAllington]

It is the sort column that is causing the issue.  I suggest you load this data in Excel, or "enter data", load it into Power Query, join it to the column in power query and load it from there. 

[Clint]

Thanks Matt.  I think I'll take the Power Query Option (and echoed by Rich below) and do it that way.  Any idea why the sort column is causing the issue though?  I've used Switch to do similar sort of indexes w/o problems so not sure what the hang up is here?