PI circular dependency error

[Varan_15]

Hi All,

I have customer tickets table like below base on entry and exit i created 2 measures (1.total hours spent  and 2. allocated sla hours) based on that i created result column which will show whether that customer within SLA, beyond SLA 

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) 

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 : 

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

Thanks in advance !

[Anonymous]

Hi @Varan_15 ,

Base on your description, it seems you got the 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 circular dependancy error.

Understanding circular dependencies in DAX - SQLBI

Avoiding circular dependency errors in DAX - SQLBI

Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community 

Best Regards

[Varan_15]

@Anonymous ,

Thanks for the update, Lest say wherever Result Beyond SLA then obiouesly having addtional Hours  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  day bucket when i tried it's coming pi cisculart dependency error.

table :

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

ex:

Beyond Time Bucket =
VAR testing = IF('Test'[M_Additional Hours] = BLANK(),0, DIVIDE( additionalday,8,0) )
VAR Day_Bucket = SWITCH(TRUE(),
testing <=2, 2,
testing >2 && testing <5, 5,
testing >5 && testing <10, 10,
11)
RETURN
Day_Bucket

for the testing expected result will be 2 day should show 1 count  ( as we have 5 hours additional hours so divide by 8 will be 0.6 then its under <2 days bucket) .

Kindly suggest.

[Varan_15]

Hi All,

Can anyone help on this issue how to achive to create calculated column using measures without PI circular dependancy.

Thanks in advance 
Varan

[Anonymous]

Hi @Varan_15 ,

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 <= 2, 2,
        testing > 2
            && testing < 5, 5,
        testing > 5
            && testing < 10, 10,
        11
    )
RETURN
    IF ( NOT ( ISBLANK ( 'Test'[M_Additional Hours] ) ), Day_Bucket )

Best Regards

[Varan_15]

@Anonymous ,

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 <= 2, 2,
testing > 2
&& testing < 5, 5,
testing > 5
&& testing < 10, 10,
11
)
RETURN
IF ( NOT ( ISBLANK ( 'Final'[M_Additional Hours]) ), Day_Bucket )

error:
<pi>A circular dependency was detected: Final[Beyond Time Bucket].</pi>

[Anonymous]

Hi @Varan_15 ,

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 <= 2, 2,
        testing > 2
            && testing < 5, 5,
        testing > 5
            && testing < 10, 10,
        11
    )
RETURN
    IF ( NOT ( ISBLANK ([M_Additional Hours] ) ), Day_Bucket )

Best Regards

[Varan_15]

@Anonymous ,

Thanks for the update, But still no luck having same error.

Please note for your information i have some more calculated columns and measures in the same table is that causing this error but while getting error it's not showing any particular column/ measure names.

Thanks,

[Anonymous]

Hi @Varan_15 ,

Could you please share your pbix file(exclude the sensitive info) with us? We will then be able to investigate the cause of the error. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Varan_15]

@Anonymous ,

Sorry to inform that the data source conected with SQL server could not be allowed to load hence i have shared sample data above.

Thanks,