Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Clint
Helper V
Helper V

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.

circular error.PNG

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
)
1 ACCEPTED SOLUTION
richbenmintz
Solution Sage
Solution Sage

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

6 REPLIES 6
richbenmintz
Solution Sage
Solution Sage

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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.

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.