Forum Discussion

Varan_15's avatar
Varan_15
Helper III
1 year ago

PI circular dependency error

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 sho...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Varan_15
Helper III
1 year ago

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 :

CustomerworkstepProducts EntryExitresult
11220inputnic01/08/2024 15:3501/08/2024 15:43within SLA
11220processnic01/08/2024 15:4305/08/2024 13:27within SLA
11220pendingnic05/08/2024 13:2705/08/2024 13:52within SLA
11220approvednic05/08/2024 13:5206/08/2024 14:05within SLA
11220closednic06/08/2024 14:0506/08/2024 15:22within SLA
566060inputllc01/07/2024 15:3501/07/2024 15:43Beyond SLA
566060processllc01/07/2024 15:4305/07/2024 13:27Beyond SLA
566060approvedllc05/07/2024 13:2705/07/2024 13:52Beyond SLA
566060closedllc05/07/2024 13:5206/07/2024 14:05Beyond 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Varan_15
      Helper III
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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's avatar
    Varan_15
    Helper III
    1 year ago

    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