PI circular dependency error

Question

Hi All,&nbsp;I have customer tickets table like below base on entry and exit i created 2 measures (1.total hours spent&nbsp; and 2. allocated sla hours) based on that i created result column which will show whether that customer within SLA, beyond SLA&nbsp;&nbsp;and then based on result i have create another measure called 3.addtional hours which will be apart from allocated sla hours how many hours customer took ( those whoever in Beyond SLA customers extra hours)&nbsp;Now i want to show that additional hours in days bucket like if one customer result beyond SLA and having additional hour 16 hours then it's under 2 day like i need to create buckets (1-2 , 3-6, 6+ ) this i want to show days bar chart with customer count under that bucket. when i tried measure / calculated column throw error Pi circular dependancy error how to achive.example table :&nbsp;CustomerworkstepProducts&nbsp;EntryExitresult11220inputnic01/08/2024 15:3501/08/2024 15:43within SLA11220processnic01/08/2024 15:4305/08/2024 13:27within SLA11220pendingnic05/08/2024 13:2705/08/2024 13:52within SLA11220approvednic05/08/2024 13:5206/08/2024 14:05within SLA11220closednic06/08/2024 14:0506/08/2024 15:22within SLA566060inputllc01/07/2024 15:3501/07/2024 15:43Beyond SLA566060processllc01/07/2024 15:4305/07/2024 13:27Beyond SLA566060approvedllc05/07/2024 13:2705/07/2024 13:52Beyond SLA566060closedllc05/07/2024 13:5206/07/2024 14:05Beyond SLA&nbsp;Thanks in advance !

anonymous · Answer

Hi&nbsp;Varan_15&nbsp;,
Base on your description, it seems you got the&nbsp;circular dependancy error when create a measure or calculated column? Could you please provide the formula of measure/calculated column which you got the error and your expected result? Later we can check it and give you a suitable solution.
In addition, you can refer the following blogs to deal with&nbsp;circular dependancy error.
Understanding circular dependencies in DAX - SQLBI
Avoiding circular dependency errors in DAX - SQLBI
&nbsp;
Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community&nbsp;
Best Regards

varan_15 · Answer

Anonymous&nbsp;,&nbsp;Thanks for the update, Lest say wherever Result Beyond SLA then obiouesly having addtional Hours&nbsp;&nbsp;in the scenario the below table second customer id result is beyond SLA so measure contains 5 hours additonal i want to create calculated clumn&nbsp; day bucket when i tried it's coming pi cisculart dependency error.table :CustomerworkstepProducts&nbsp;EntryExitresult11220inputnic01/08/2024 15:3501/08/2024 15:43within SLA11220processnic01/08/2024 15:4305/08/2024 13:27within SLA11220pendingnic05/08/2024 13:2705/08/2024 13:52within SLA11220approvednic05/08/2024 13:5206/08/2024 14:05within SLA11220closednic06/08/2024 14:0506/08/2024 15:22within SLA566060inputllc01/07/2024 15:3501/07/2024 15:43Beyond SLA566060processllc01/07/2024 15:4305/07/2024 13:27Beyond SLA566060approvedllc05/07/2024 13:2705/07/2024 13:52Beyond SLA566060closedllc05/07/2024 13:5206/07/2024 14:05Beyond SLA&nbsp;ex:Beyond Time Bucket =VAR testing = IF('Test'[M_Additional Hours] = BLANK(),0, DIVIDE( additionalday,8,0) )VAR Day_Bucket = SWITCH(TRUE(),testing &lt;=2, 2,testing &gt;2 &amp;&amp; testing &lt;5, 5,testing &gt;5 &amp;&amp; testing &lt;10, 10,11)RETURNDay_Bucket&nbsp;for the testing expected result will be 2 day should show 1 count&nbsp; ( as we have 5 hours additional hours so divide by 8 will be 0.6 then its under &lt;2 days bucket) .&nbsp;Kindly suggest.

anonymous · Answer

Hi&nbsp;Varan_15&nbsp;,
I created a sample pbix file(see the attachment), please check if that is what you want.
Beyond Time Bucket =
VAR additionalday =
    CALCULATE (
        SUM ( 'Test'[M_Additional Hours] ),
        FILTER ( 'Test', 'Test'[result] = "Beyond SLA" )
    )
VAR testing =
    IF (
        NOT ( ISBLANK ( 'Test'[M_Additional Hours] ) ),
        DIVIDE ( additionalday, 8, 0 )
    )
VAR Day_Bucket =
    SWITCH (
        TRUE (),
        testing &lt;= 2, 2,
        testing &gt; 2
            &amp;&amp; testing &lt; 5, 5,
        testing &gt; 5
            &amp;&amp; testing &lt; 10, 10,
        11
    )
RETURN
    IF ( NOT ( ISBLANK ( 'Test'[M_Additional Hours] ) ), Day_Bucket )

Best Regards

varan_15 · Answer

Anonymous&nbsp;,&nbsp;Thanks for your effort, please note M_additional_Hours field is a measure and when i used the same again getting same error.as below calculated column :Beyond Time Bucket =VAR additionalday =CALCULATE ('Final'[M_Additional Hours],FILTER ( 'Final', 'OM_TI Final'[M_SLA_Result] = "Beyond SLA" ))VAR testing =IF (NOT ( ISBLANK ( 'Final'[M_Additional Hours] ) ),DIVIDE ( additionalday, 8, 0 ))VAR Day_Bucket =SWITCH (TRUE (),testing &lt;= 2, 2,testing &gt; 2&amp;&amp; testing &lt; 5, 5,testing &gt; 5&amp;&amp; testing &lt; 10, 10,11)RETURNIF ( NOT ( ISBLANK ( 'Final'[M_Additional Hours]) ), Day_Bucket )&nbsp;error:&lt;pi&gt;A circular dependency was detected: Final[Beyond Time Bucket].&lt;/pi&gt;

anonymous · Answer

Hi&nbsp;Varan_15&nbsp;,
Please update the formula of measure [Beyond Time Bucket] as below and check if it can return the correct result...
Beyond Time Bucket = 
VAR additionalday =
  SUMX(
       FILTER ( 'Test', 'Test'[result] = "Beyond SLA" ),
       [M_Additional Hours]
    )
VAR testing =
    IF (
        NOT ( ISBLANK ( 'Test'[M_Additional Hours] ) ),
        DIVIDE ( additionalday, 8, 0 )
    )
VAR Day_Bucket =
    SWITCH (
        TRUE (),
        testing &lt;= 2, 2,
        testing &gt; 2
            &amp;&amp; testing &lt; 5, 5,
        testing &gt; 5
            &amp;&amp; testing &lt; 10, 10,
        11
    )
RETURN
    IF ( NOT ( ISBLANK ([M_Additional Hours] ) ), Day_Bucket )

Best Regards

Forum Discussion

PI circular dependency error

9 Replies

Related content

Circular dependency

Circular Dependency Error

Circular Dependency

Circular dependency error

Error: circular dependency

Customer	workstep	Products	Entry	Exit	result
11220	input	nic	01/08/2024 15:35	01/08/2024 15:43	within SLA
11220	process	nic	01/08/2024 15:43	05/08/2024 13:27	within SLA
11220	pending	nic	05/08/2024 13:27	05/08/2024 13:52	within SLA
11220	approved	nic	05/08/2024 13:52	06/08/2024 14:05	within SLA
11220	closed	nic	06/08/2024 14:05	06/08/2024 15:22	within SLA
566060	input	llc	01/07/2024 15:35	01/07/2024 15:43	Beyond SLA
566060	process	llc	01/07/2024 15:43	05/07/2024 13:27	Beyond SLA
566060	approved	llc	05/07/2024 13:27	05/07/2024 13:52	Beyond SLA
566060	closed	llc	05/07/2024 13:52	06/07/2024 14:05	Beyond SLA