Circular dependency was detected: Lookupvalue

[Anonymous]

Hi,

Wondering if you could help. I have an issue where I have a calculated column as follows:

TimeCost = (LOOKUPVALUE(Training_List[Est. time],Training_List[TID],Training_Req[TID])*LOOKUPVALUE(Staff_List[SCR Rate],Staff_List[Staff No],Training_Req[Staff number]))

I have then created a calculated column as follows:

ExpCost = LOOKUPVALUE(Training_List[Est. Expense],Training_List[TID],Training_Req[TID])

The first column works as expected but unfortunately, when I try to execute the second column, I get the following error:

"A circular dependency was detected: Training_Req[TimeCost], Training_Req[ExpCost], Training_Req[TimeCost]."

I'm not sure where this circular dependency is being created... could anyone help please? I have tried looking at various solutions but am at a bit of a loss on what I can do. I would share the template file or data but unfortunately this is protected information that I cannot share.

Many thanks in advance!

[v-frfei-msft]

Hi @Anonymous ,

Please refer to this third - party bolg about circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

In your case, Power BI does not allow to have two calculated columns that contain measures that are also based on that table. In order to understand why, you'd need a better understanding of what's going on under the hood.

In order to get around it, you should try turning these into measures that reference your calcllated columns.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[Anonymous]

Hi Ashish,

I can't share specific data so i'll generate information to mimic this issue.

Say I have:

table A (Training List) which has pre-populated columns training ID, training hrs, training expense and training fee where training ID is the key column.

table B (Staff List) which has pre-populated columns staff no, staff rate where J is the key column.

table C (training req) which has columns training id, staff no

This is imported into PowerBI from Excel.

I am creating two columns in table C, as follows:

TimeCost = LOOKUP(table_A[training hrs],table_A[training ID],table_C[training ID])*LOOKUP(table_B[staff rate],table_B[staff no],table_C[staff no])

ExpCost = LOOKUP(table_A[training expense],table_A[training ID],table_C[training ID])+LOOKUP(table_A[training fee],table_A[training ID],table_C[training ID])

Both formulas seem to work fine and whatever I choose in the first column works. However, when I open the second column to add information in, I get the following error:

A circular dependency was detected: Table_C[TimeCost], Table_C[ExpCost], Table_C[TimeCost].

I know a circular dependency is one depending on another which then makes it difficult to manage but in neither of these columns do I reference TimeCost or ExpCost so I don't see where the circular reference is.

Could someone help please?