Forum Discussion
Circular dependency error using switch
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:
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 -1Hope 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!
6 Replies
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.
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?
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 -1Hope 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!Thanks Richard, I will do this in Power Query. Much appreciated.
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.
