Circular Dependency

[mattymc1984]

Hi all,

I'm an extremely new user and was hoping for a bit of help.  I've two really simple calculated columns, one for "Division" and another for "Company Name".  My data is an aged debtor report with a company code from SAP.  I've also loaded a mapping table that maps company code to division and company name.

My lookups are as follows:

Division = LOOKUPVALUE(Mapping[Division],Mapping[Company],'SAP data'[Company Code])

Company = LOOKUPVALUE(Mapping[Company],Mapping[Company Code],'SAP data'[Company Code])

Which ever one I entter first works fine, but the second one falls over and claims a "circular dependency has been detected: SAP data[Division], SAP data[Company], SAP data[Division]".  This is driving me crazy, I can't see how this is circular, one column looks up company code and provides mapping A, the next column looks up company code and provides mapping B.

Any help appreciated,

[MattAllington · Accepted solution]

This is very complex to explain and it is related to how the engine uniquely identifies each row in a table. If the table doesn't have a primary key, every column in the table  is used as the key instead. So if the first calc column works fine, but then the second calc column needs to use all other columns including the calc column, and vice versa. There are 2 solutions

best is to sort this out before loading using power query (get data). This is where this type of work belongs. If you must do it in Power Pivot, then add an ID key column to the table and set that as the primary key (I think there is a menu item for this, but I have never done it - see previous point)

[mattymc1984]

I'll deal with all mapping before importing the data.  I just wanted to see if it was possible to do this in Power BI in order to automate another step of my reporting.

Many thanks,

Matt

[vitinho]

But why in the first you are searching for the 'SAP data'[Company Code] in the Mapping[Company] and the other one in Mapping[Company Code]?

[mattymc1984]

Thanks - good point, that is incorrect, however it still doesn't work if I lookup the company code.