Remove circular dependency from calculated column

[Winter_king94]

Hello, I have converted an excel formula into a calculated column. This calculated column (Lookup Customer Sector) produces the exact results I need, which is combining the sector from multiple tables based on various fields into one spot.
I'm newer to PBI and the easiest way for me to validate & create this column was by using variables.
The problem I am running into is that I need to use this calculated column to join with another table, but when I go to make the relationship I get the error that 'Lookup Customer Sector' can't be used due to circular dependencies. 

Can someone please help me figure out a way to write this calculated column that removes the circular dependencies?

Lookup Customer Sector =

VAR Step1 = IF('OBIEE - COGS'[End Customer Account Number] = "141398", "Medical - Aesthetic Therapy",

                IF((LEFT('OBIEE - COGS'[Item Num],2) = "LQ" || LEFT('OBIEE - COGS'[Item Num],3) = "BLQ" || LEFT('OBIEE - COGS'[Item Num],4) = "RPLQ"), "Spec Ind - Liquid Cool Elect", BLANK()))

VAR STEP2 = IF( Step1 = BLANK(), LOOKUPVALUE('Customer Sector (Product Line) Key'[Sector], 'Customer Sector (Product Line) Key'[Product Line], 'OBIEE - COGS'[PRODUCT_HIER_GROUPING]), Step1)

VAR STEP3 = IF( Step2 = BLANK(), LOOKUPVALUE('Customer Sector (Item#) Key'[Sector], 'Customer Sector (Item#) Key'[Item#], 'OBIEE - COGS'[Item Num]), STEP2)

VAR STEP4 = IF(STEP3 = BLANK(), LOOKUPVALUE('Oracle End Cust #'[End Customer Sector], 'Oracle End Cust #'[End Customer Account Number], 'OBIEE - COGS'[End Customer Account Number]), STEP3)

VAR STEP5 = IF(STEP4 = BLANK(), LOOKUPVALUE('Customer Sector (Account) Key'[Sector], 'Customer Sector (Account) Key'[Account Number], 'OBIEE - COGS'[Bill to Customer Account Num]), STEP4)

VAR STEP6 = IF(AND(STEP5 = BLANK(), LEFT('OBIEE - COGS'[PRODUCT_HIER_GROUPING],2) = "LQ"), "Spec Ind - Liquid Cool Elect", STEP5)

VAR STEP7 = IF(STEP6 = BLANK(), 'OBIEE - COGS'[Customer Sector], STEP6)    

RETURN

STEP7

This is what the field looks like and gets used in visuals with other fields such as item number, end customer, bill to customer, etc.

thanks in advance!

[Winter_king94]

thank you! I will work on moving this over to Power Query

[krishnakanth240]

You're welcome @Winter_king94! Sure

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.  Explain the business logic clearly.

[lbendlin]

I have converted an excel formula into a calculated column

Don't do that. Power BI is not Excel.  Formulate the business problem and then design a native solution based on Power BI's capabilities.

As @krishnakanth240 mentioned one of the recommended approaches to get rid of the circular dependency is to push that column generation up into Power Query.

[krishnakanth240 · Accepted solution]

Hi @Winter_king94 

Because calculated column uses LOOKUPVALUE across tables that are related to same table creating a dependency loop.

So, do not use a calculated column and convert it into a measure or power query column. Move this logic to power query, merge tables step by step like your VAR logic and create the column there