Circular Dependency error

[Anonymous]

Hello, 

I am attempting to write a DAX expression that references two calculated columns and one other column from the source data. The error message I receive is as follows:

A circular dependency was detected: Sell Out[Status Country Name], Sell Out[FINAL], Sell Out[Status Country Name].

FINAL is the new calculated column I am trying to create, using the following DAX:

FINAL = if([Status]="Sustainable" && [Country]<>[Status Country Name],"Not Sustainable",[Status])

Note that [Status Country Name] is also a calculated column with expression:

Status Country Name = lookupvalue('Country Code Mapping'[Country Name],'Country Code Mapping'[Country Code],[Status Country Code])

As is [Status]:

Status = left([Mapping Lookup],11)

The basis for all of these calculated columns is [Mapping Lookup]:

Mapping Lookup = lookupvalue('Sustainability Mapping'[Sustainable By Lyreco],'Sustainability Mapping'[ Lyreco SAP code],[SAP Code])

I am struggling to identify what the circular depency is. [Status Country Name] does not depend on [FINAL] whereas [FINAL] is dependent upon [Status Country Name]. 

Thank you in advance for any support

[MFelix]

Hi @Anonymous,

You have a circular dependency because of the table context and this can be influenced by the relationships within your model, not only the row context.

How is your model setup do you have any relationship between the tables you are making the lookupvalues?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Thanks for the response. Regarding the lookupvalues here is the relationship between tables:

Where 'Sustainability Mapping' has unique rows based on unique SAP codes. If you still need a sample of the data set please let me know, I will have to create a mockup as the actual data source is business sensitive. 

[MFelix · Accepted solution]

Hi @Anonymous ,

Taking into account that you have relationships between the tables you can change the LOOKUPVALUE by a RELATED making the circular dependency to be eliminated:

Status Country Name = RELATED('Country Code Mapping'[Country Name])

[MFelix]

If you can send the mockup by private message that would be great.