Forum Discussion

Hasan_iba's avatar
Hasan_iba
Helper II
9 years ago
Solved

Circular reference error

Hi power bi experts!! I am having a circular reference error while trying to calculare SPI and CPI coloumn. SPI =Cumulative BCWP/Cumulative BCWS. I have figured out that the cumulative BCWP is the source of the problem. I just can not fix the circular referecne error. It will be great if you can fix the issue. I have attached the file here:  https://drive.google.com/file/d/0B0XwYvmbIN12cktTaHZpTi1IZTQ/view?usp=sharing

A promp solution will be relaly helpful. Thank you. 

Regards

Hasan

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Hasan_iba,

     

    Based on my research, there are normally two ways to solve the circular dependency issue:

    • Add the row identifier column to one of your fact tables, then create corresponding relationships with other fact tables.
    • Use ALLEXCEPT to remove the calculated columns from the set of columns that become part of the dependency list is a viable option.

    For more details about the circular dependency issue, you can refer to this article.

     

    In addition, after looking into your shared pbix file, I would suggest you try using Merge Queries options in Query Editor to merge your "TaskTimephasedDataSet" table and "TaskBaselineTimephasedDataSet" table into a single table, which should make your data mode clear and easier to handle. Here is an article about how to use Merge Queries option for your reference.:smileyhappy: 

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Hasan_iba,

     

    Based on my research, there are normally two ways to solve the circular dependency issue:

    • Add the row identifier column to one of your fact tables, then create corresponding relationships with other fact tables.
    • Use ALLEXCEPT to remove the calculated columns from the set of columns that become part of the dependency list is a viable option.

    For more details about the circular dependency issue, you can refer to this article.

     

    In addition, after looking into your shared pbix file, I would suggest you try using Merge Queries options in Query Editor to merge your "TaskTimephasedDataSet" table and "TaskBaselineTimephasedDataSet" table into a single table, which should make your data mode clear and easier to handle. Here is an article about how to use Merge Queries option for your reference.:smileyhappy: 

     

    Regards

    • Hasan_iba's avatar
      Hasan_iba
      Helper II
      9 years ago
      Hello! Thank you so much for the detailed explanation. I did bring one required column in the main table from other table and it worked. Much appreciated. Thanks
  • Hasan_iba's avatar
    Hasan_iba
    Helper II
    9 years ago

    Hello Power BI community. I am getting circular reference error. I need to calculate SPI which is equal to cumulative BCWP/cumulative BCWS. I need to have SPI as a calculated column. I have figured out that the source of the circular reference is the cumulatiive BCWP column but cant figure out the problem of circular reference. The pbix file is in the link: https://drive.google.com/file/d/0B0XwYvmbIN12cktTaHZpTi1IZTQ/view?usp=sharing

    A promp help will be much appreciated. 

    Thank you