Sum with relatedtable and Userelationship - circular dependency error referencing a single column

[Teaman]

Hi all,

I've got a BOL table (list of components and quantites that go together to make a material) and I'm trying to get the total component quantity. I know this is very simple to get with a table in the report view but I need this information as a custom column as other custom columns need to reference it in the future. Below is a simplified version.

BOM

Dim_Material

Relationships

I want the 'Total Quantity' column in Dim_Material to show this

I've tried a few things and I believe the below should work but I'm getting a circular dependency error. The userelationship is required as the actual data includes a lot more data where the dim_material references the BOM_material as the main relationship. This error seems strange to me as it's only referencing one column which is itself - I've search the community and not found any errors like this. 

How do I get the total components quantity in this calculated column?

Any help is greatly appreciated.

Thanks,

Greg

[DataInsights · Accepted solution]

@Teaman,

Try this calculated column in table Dim_Material. It works with or without relationships.

Total Quantity = 
VAR vMaterial = Dim_Material[Material]
// determine if material or component
VAR vType =
    IF (
        ISEMPTY ( FILTER ( BOM, BOM[Material] = vMaterial ) ),
        "Component",
        "Material"
    )
VAR vResult =
    SWITCH (
        vType,
        "Material", SUMX ( FILTER ( BOM, BOM[Material] = vMaterial ), BOM[Quantity] ),
        SUMX ( FILTER ( BOM, BOM[Component] = vMaterial ), BOM[Quantity] )
    )
RETURN
    vResult

[Teaman]

Hi @DataInsights ,

That worked perfectly. Thanks for the help!