A circular dependency was detected

[o59393]

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 time * Total hours to discount

The error I get is the following:

A circular dependency was detected:

I attach the pbix to better visibility. The syntax from my point of view is correct but don't know what could be wrong.

https://1drv.ms/u/s!ApgeWwGTKtFdh13Pj8JD18lE9HC8?e=XhCfRd

Regards.

[o59393]

Hi @PaulDBrown 

I understood from the video that I need to reduce dependency list of the column, to consider only the needed column.

I am doing the all except in my 'Mix' calculated column and still not working.

So, on excel would be this:

And translated to dax I get:

The error on my 'Capacity' calculated column is directly related to my Mix calculated column above.

The capacity column is not a calculate function, just an IF. So I guess the root cause must be on the mix column.

Do you know what can I do based on my code?

Thanks.

[PaulDBrown]

I would try wrapping the IF column in CALCULATE with ALLEXCEPT. Actually, you should do so with all calculated columns: the video states that all calculated columns need the ALLEXCEPT

[o59393]

Hi @PaulDBrown 

If I try to wrap the if colum with calculate, this happens:

Columns are underlined with red.

Regards.

[PaulDBrown]

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]

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]

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]

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]

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]

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]

Hi @PaulDBrown '

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

Regards.

[PaulDBrown]

Check out this  video by the gurus at sqlbi.com on circular dependencies: