Calculated columns not working due to circular dependency

[o59393]

Hi

I am doing two calculated columns and I am getting a circular dependency error in one of them.

The 'Capacity Real' column is basically a multiplication of =

[BPM Conversion Real] * [Mix by product] * Max hours column * CIP hours column 

And the 'Capacity Goal' columm multiples = 

[BPM Conversion Goal] * [Mix by product] *  Max hours column * CIP hours column 

How can I make it work? 

Pbix is attached below:

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

Regards.

[mahoneypat]

I looked at your file, and commented out parts of your expression to see which is causing the dependency error.  Both the measures part and the LOOKUPVALUE parts cause it.  Looking at your columns, you have multiple pivoted columns that have similar data in them (e.g., Jan and Feb data).  You really should improve your model (unpivot and set up 1:M relationships with single direction filters).  Having a simpler model leads to simpler DAX.

Pat

[o59393]

Hi @mahoneypat 

Correct I have multiple pivoted columns in 'Hours by line' table. From January to December. I remain it that way because it allows me to keep the column 'Plant-Line-Package' with unique values and make the bridge between tables.

I did change my relationship from 1:M :

I tried using the calculate underneath the IF statement but still shows red:

Can you further advise? 

Thanks!

[o59393]

Hi @mahoneypat 

Can you please further advise once I change to 1:M ?

How can I make this circular dependency error work.

Thanks. 

[mahoneypat]

I looked at it some more, but this model needs to much work.  I could avoid the circ error by using REMOVEFILTERS with the two measures, but it the LOOKUPVALUES in multiple calculated columns is also causing a circ error.  You should improve your model and/or consider using RELATED to get a value from the other table instead of LOOKUPVALUE.

Pat

[o59393]

Hi @mahoneypat 

I changed it to RELATED. Please have a look to the pbix.

Regards,

[mahoneypat · Accepted solution]

This column expression avoids the error, but I definitely encourage you to fix your model.  Simple model, simple dax.

Capacity Goal =
VAR bpmgoal =
    CALCULATE (
        [BPM Conversion Goal],
        REMOVEFILTERS ( 'SKU by line - Official'[Capacity Real] )
    )
VAR mix =
    CALCULATE (
        [Mix by product],
        REMOVEFILTERS ( 'SKU by line - Official'[Capacity Real] )
    )
VAR result =
    IF (
        'SKU by line - Official'[Month] = 1,
        (
            bpmgoal * mix
                * RELATED ( 'Hours by line - Official'[Max available time January (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**] )
                * RELATED ( 'Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - January] )
        ) * 60,
        IF (
            'SKU by line - Official'[Month] = 2,
            (
                bpmgoal * mix
                    * RELATED ( 'Hours by line - Official'[Max available time February (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**] )
                    * RELATED ( 'Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - February] )
            ) * 60,
            0
        )
    )
RETURN
    result

Pat

[o59393]

Awesome @mahoneypat it worked!!

I tried your advise to make the model simpler. So I unpivoted the columns:

Did a M:M relationship cause it was the only way to bridge the tables:

And then I updated the code with filter:

Capacity Real = 

IF (
'SKU by line - Official'[Month] = 1,
(
[BPM Conversion Real] * [Mix by product]
* 
FILTER(
    'Hours by line - Official',
RELATED( 
    'Hours by line - Official'[Attribute]) = "Hours by line - Official'[Max available time January (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**]"
)
* 
FILTER(
    'Hours by line - Official',
RELATED( 
    'Hours by line - Official'[Attribute]) = "Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - January]"
)
) * 60,
IF (
'SKU by line - Official'[Month] = 2,
(
[BPM Conversion Real] * [Mix by product]
* 
FILTER(
    'Hours by line - Official',
RELATED( 
    'Hours by line - Official'[Attribute]) = "Hours by line - Official'[Max available time February (hours) [Calendar days - Non working days) * # of max shifts possible * hours per **bleep**]"
)
* 
FILTER(
    'Hours by line - Official',
RELATED( 
    'Hours by line - Official'[Attribute]) = "Hours by line - Official'[Total hours to discount (CIP and Mold Changes) - February]"
)
) * 60,
0
)
)

I got the following error:

The column 'Hours by line - Official[Attribute]' either doesn't exist or doesn't have a relationship to any table available in the current context.

In case you wanna have a look at the pbix with the unpivoted columns.

You still solved my original problem and I really appreciate your help!

[o59393]

Hi @mahoneypat 

Ok let me try to use RELATED instead and come back to you.

Thanks.

[mahoneypat]

Please see how to remove filters from your dependent column(s) in this article/video.

Avoiding circular dependency errors in DAX - SQLBI

Pat

[o59393]

Hi @mahoneypat 

From what I've seen I have to use Calculate and AllExcept, as soon as I use calculate my column goes like this:

I am not sure what should I exclude with all AllExcept.

The capacity column is calculated with 2 measures and 2 columns from the other table.

Thanks.