Forum Discussion
Circular dependency error using switch
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.
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.