Forum Discussion

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

Calculated columns not working due to circular dependency

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] * [M...
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
4 years ago

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!