Strange Circular Dependency issue

[DouweMeer]

So I'm trying to account for the error in package size maintained by us, versus the resellers. 

My expression:

Selling price correction =

VAR test = LOOKUPVALUE( 'ALL PRODUCTS'[Pieces/case] , 'ALL PRODUCTS'[Item] , 'EMEA - Standard_1'[Product] )

RETURN

if (

   and (

      'EMEA - Standard_1'[Selling Price] / test < 'EMEA - Standard_1'[List Price]

      , 'EMEA - Standard_1'[Selling Price] / test > 'EMEA - Standard_1'[Adjusted Cost]

      )

   , 'EMEA - Standard_1'[Selling Price] / test

   , 'EMEA - Standard_1'[Selling Price]

   )

It suggest that there is a circular dependency detected. This expression is placed in a customn column of 'EMEA - Standard_1'. The field [Selling Price], [List Price], [Adjusted Cost], [Pieces/case], [Item] and [Product] do not contain any calculation, nor are they measures. 

Any suggestion to the why this is happening?

[v-lili6-msft]

HI, @DouweMeer 

For this error, it must be that one of 'ALL PRODUCTS'[Pieces/case], 'EMEA - Standard_1'[Selling Price], 'EMEA - Standard_1'[List Price] or  'EMEA - Standard_1'[Adjusted Cost] is calculate column and it is calculated based on anther table.

So just adjust it in the formula.

If you still have the problem, please share a simple sample pbix file.

Best Regards,

Lin

[DouweMeer]

@v-lili6-msft 

I understand your theory as I was looking for that as well. But those fields are not calculated. 

Before this case, I also had the same issue with another expression:

lookupvalue ( all products [piece/case] , all product [item] , 'emea...' [product] )

where it would hit me with a circular dependency. I can't imagine a way where you can be hit a circular dependency when using a lookupvalue on a newly created calculated column. 

I fixed the previous circular dependency by creating a dynamic table and using that dynamic table to do a:

sum ( dynamic table , value( dynamic table [piece price] ) 

To me, it tastes like a bug. Think I'm going to do the dynamic table trick again to fix this crap. 

[v-lili6-msft]

hi, @DouweMeer 

Could you please share a simple sample pbix file for us to have a test 

Best Regards,

Lin

[DouweMeer]

@v-lili6-msft 

Actually, I didn't though I could reproduce it. That is what always happens when I have these strange cases. But I'm hit with the problem that I can't attach attachments to the forum and other apps that will let me do it, are disabled by my company. Any chance you can pm me that I can send you the file?

Otherwise, I think I found the bugger. But I still don't know why. 

When I try to enable the 'both' relationshipWhere the problem is and its expression

[v-lili6-msft · Accepted solution]

hi, @DouweMeer 

It is not a bugger, it is a common problem between calculate column and relationship

https://www.sqlbi.com/articles/understanding-circular-dependencies/

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

You can upload sample pbix file and expected output to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[DouweMeer]

OneDrive with external link is block by policy. Sorry, not going to work.