Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Circular dependency issue

I'm getting a circular dependency error that I can't figure out for the life of me.  To start, I have a fairly simple formula to calculate a value on a daily basis: 

(field1 + field3)/ (field1 + field2)

 

I want to display that as a daily value, as well as month-to-date and year-to-date. Using my actual field names, this is the formula:

 

prod_mwh + lost_prod_ext_mwh / prod_mwh + lost_prod_mwh
 
I then create measures for MTD and YTD which look like this (and maybe this is wrong and is part or all of the problem?):
 
oe_wo_ext_pct_mtd = DIVIDE(TOTALMTD(sum(oe_summary_report[prod_mwh]),oe_summary_report[date]) + TOTALMTD(sum(oe_summary_report[lost_prod_ext_mwh]),oe_summary_report[date]),TOTALMTD(sum(oe_summary_report[prod_mwh]),oe_summary_report[date]) + TOTALMTD(sum(oe_summary_report[lost_prod_mwh]),oe_summary_report[date]),0)
 
oe_wo_ext_pct_ytd = DIVIDE(TOTALYTD(sum(oe_summary_report[prod_mwh]),oe_summary_report[date]) + TOTALYTD(sum(oe_summary_report[lost_prod_ext_mwh]),oe_summary_report[date]),TOTALYTD(sum(oe_summary_report[prod_mwh]),oe_summary_report[date]) + TOTALYTD(sum(oe_summary_report[lost_prod_mwh]),oe_summary_report[date]),0)
 
I then have a target % field, which isn't calculated, it's just a static field for each plant.
 
So far, so good, my table looks like this:
 
Now I need to show variances between Daily, MTD, and YTD vs. the Target. I create daily and MTD columns like this:
 
Daily Var = oe_summary_report[oe_wo_ext_pct] - oe_summary_report[oe_target_wo_ext_pct]
Monthly Var = oe_summary_report[oe_wo_ext_pct_mtd] - oe_summary_report[oe_target_wo_ext_pct]
 
And this is good so far:
 
But when I create my YTD field, I get a circular reference error:
 
I assume I'm doing something simple and obvious wrong, but can't figure it out. This is the last thing I need to finish a major project. Any ideas?
 
Thank you!
Alex
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Alex,

     

    If we delete the column [oe_wo_mtd_compare], the [YTD Var] should work. I think you should create a measure instead. Which reference isn't available? Is it below you need?

    Circular-dependency-issue

    It's better to create a date to work with the time intelligence functions. 

     

    Best Regards,

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Alex,

     

    Did you create a column instead of a measure accidentally? 

    Can you share a sample? Please mask the sensitive data first. Just see from your formula, it's simple and clear. 

     

    Best Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-jiascu-msft

       

      Sorry, I forgot to include the file, which is here.

       

      I created a column rather than measure because not all of the fields I wanted to reference were available when I tried to use a measure - that must be part of the problem. I spent some time this weekend learning DAX a little more deeply to see if I can get to the bottom of this, and suspect that the problem is that when I compare my MTD or YTD fields to the target value, the target value isn't an aggregate, it's just a static field on every row of data. So with MTD and YTD being aggregates, I'm thinking Power BI may not be able to find a specific row to get the target from. I'm going to try applying an average to that field and see if that might work.

       

      If you do have time to take a look and can offer any other insight, it's much appreciated!

       

      thanks

      Alex

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Alex,

         

        If we delete the column [oe_wo_mtd_compare], the [YTD Var] should work. I think you should create a measure instead. Which reference isn't available? Is it below you need?

        Circular-dependency-issue

        It's better to create a date to work with the time intelligence functions. 

         

        Best Regards,