Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
5 years ago

A circular dependency was detected

Hi all   I am building a new column on the table SKU by line - Official, which basically is a multiplication of:   A measure [BPM conversion]* a calculated column [Mix by product] * Max available...
Need Help
Show and Tell
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Yes, sorry, I was posting from my phone so couldn't see the image clearly.
I would try the 'SKU by line - Official' [Month] field for the ALLEXCEPT. It's hard to tell because not all the table fields are visible in the image).
Ideally it should be the field with the highest granularity in the table.

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
5 years ago

Hi PaulDBrown 

 

Can you please have a look at my pbix?

 

I don't know how to use calculate + allExcept with an if function:

 

 

Can you please further advise?

 

Thanks!

 

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    OK, first of all, can you check both calculated columns? they appear to calculate the same value.

    Secondly, is there a particualr reason you need these calculations as calculated columns? they appear to be best suited as measures (unless you are going to filter by the resulting values)

     

    To answer your question, this code works:

    Capacity Real =
    VAR IFStatment = IF (
    'SKU by line - Official'[Month] = 1,
    (
    [BPM Conversion Real] * [Mix by product]
    * LOOKUPVALUE (
    'Hours by line - Official'[Max available time January (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**],
    'Hours by line - Official'[Plant-Line-Package], 'SKU by line - Official'[Plant-Line-Package]
    )
    * LOOKUPVALUE (
    'Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - January],
    'Hours by line - Official'[Plant-Line-Package], 'SKU by line - Official'[Plant-Line-Package]
    )
    ) * 60,
    IF (
    'SKU by line - Official'[Month] = 2,
    (
    [BPM Conversion Real] * [Mix by product]
    * LOOKUPVALUE (
    'Hours by line - Official'[Max available time February (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**],
    'Hours by line - Official'[Plant-Line-Package], 'SKU by line - Official'[Plant-Line-Package]
    )
    * LOOKUPVALUE (
    'Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - February],
    'Hours by line - Official'[Plant-Line-Package], 'SKU by line - Official'[Plant-Line-Package]
    )
    ) * 60,
    0
    ))
    RETURN
    CALCULATE(IFStatment, ALLEXCEPT('SKU by line - Official', 'SKU by line - Official'[Month]))
     
    Nevertheless, please check the code for both, and please explain why these need to be calculated columns.
  • o59393's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    hi PaulDBrown 

     

    Yes, I might need to filter by the resulting value too. 

     

    I tried your code but it still says there is a circular dependency 😞

     

    Please see attached the pbix updated.

     

    When you say both columns calculate the same value, what do you mean?


    Thank you for the help, really apppreciate it.

  • o59393's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    Hi PaulDBrown '

     

    Did you have a chance to see the pbix above? The error still displays with your code.

    Regards.