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

Apologies since I can't see the image clearly. However, try putting the IF expression in a VAR and then do the CALCULATE with ALLEXCEPT as the RETURN expression 

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

Hi PaulDBrown 

 

You can click on the image and it will expand. What column should I use within allexcept?

 

Please click in image below to zoom in:

 

 

Regards.

  • 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.