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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to solve circular dependency

My issue regards a circular dependency I am not able to understand. Here's my relationship pattern:

 

 

Relationship.png

 

In table Margine I built 2 calculated columns which I copy down here:

 

 
CNTMRG_AvailableCredits = 
RELATED(Contratti[CNT_Credits])
+ CALCULATE(SUM('Extra crediti'[EXTCRD_Credits]) ; 
FILTER('Extra crediti' ; Margine[CNTMRG_ContractId] = 'Extra crediti'[EXTCRD_ContractId] && YEAR('Extra crediti'[EXTCRD_Date]) < Margine[CNTMRG_Year] || (YEAR('Extra crediti'[EXTCRD_Date]) = Margine[CNTMRG_Year] && MONTH('Extra crediti'[EXTCRD_Date]) <= Margine[CNTMRG_Month]))) - CALCULATE(SUM(Tickets[TKT_UsedCredits]) ;
FILTER(Tickets ; Tickets[TKT_ContractId] = Margine[CNTMRG_ContractId] && (Tickets[TKT_InsertYear] < Margine[CNTMRG_Year] || (Tickets[TKT_InsertYear] = Margine[CNTMRG_Year] && Tickets[TKT_InsertMonth] <= Margine[CNTMRG_Month])) && Tickets[TKT_Type]="SRQ" && (Tickets[TKT_Solution] = "Resolved" || Tickets[TKT_Solution] = "None" || (Tickets[TKT_Solution] = "WontFix" && DISTINCTCOUNT('Attività'[ACT_Id])<>0))))

 

 

CNTMRG_Margin = 
Margine[CNTMRG_ContractFee] * IF(Margine[CNTMRG_Year] < YEAR(TODAY()) || (Margine[CNTMRG_Year] = YEAR(TODAY()) && Margine[CNTMRG_Month] <= MONTH(TODAY()));1;0)
+ CALCULATE(SUM('Extra crediti'[EXTCRD_Price]) ; 
FILTER('Extra crediti' ; YEAR('Extra crediti'[EXTCRD_Date]) = Margine[CNTMRG_Year] && MONTH('Extra crediti'[EXTCRD_Date]) = Margine[CNTMRG_Month])) - CALCULATE(SUM(Tickets[TKT_OreTotaliAttività]) * 35 ;
FILTER(Tickets ; Margine[CNTMRG_ContractId] = Tickets[TKT_ContractId] && Tickets[TKT_InsertYear] = Margine[CNTMRG_Year] && Tickets[TKT_InsertMonth] = Margine[CNTMRG_Month] && Tickets[TKT_Type] = "SRQ" && (Tickets[TKT_Solution] = "Resolved" || Tickets[TKT_Solution] = "None" || (Tickets[TKT_Solution] = "WontFix" && DISTINCTCOUNT('Attività'[ACT_Id])<>0))))

 

 
Both columns refer to other tables in my pattern but I don't detect any circular dependency between them. However, I receive the following error message:
 
"A circular dependency was detected: Margine[CNTMRG_AvailableCredits], Margine[CNTMRG_Margin], Margine[CNTMRG_AvailableCredits]"
 
I can't understand why. I read articles from sqlbi.com and around the Web but I didn't find anything useful.
 
Please can you help me? Ask me for more information if necessary
3 REPLIES 3
Anonymous
Not applicable

@Anonymous,

This happens when you have context transition happens in your calculated column, and your table that contains the calculated column does not have a primary key. You can look at this article to have a better understanding of circular dependency.

Anonymous
Not applicable

@Anonymous,

Since you have context transition (using CALCULATE) in the two calculated columns of the many side of the relationship, calculated column 1 depends on all the columns in the Margin table + calculated column 2. Same thing happens to calculated column, where it depends on all the columns in the Margin + calculated column 1. This leads to circular dependency that explains in the article. 

Anonymous
Not applicable

@Anonymous 

 

Thanks for answering. Unfortunately I already read it, I understand what circular dependency means, but I don't find out why it affects my situation. I am not able to detect where it takes me.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.