Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mattymc1984
Advocate I
Advocate I

Circular Dependency

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,

 

 

1 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)



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4

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)



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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
Helper I
Helper I

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]?

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors