Circular Dependency

[AntoineB]

Hi,

I'm currently facing a circular Depency error working on a Power Bi report. I have one sheet in this report when two columns seem to be stuck in a circular dependency according to the error message.

Here are the to formulas of the calculated columns :

Couverture Collisage = RELATED('colisage'[colisage])/AVERAGEX(RELATEDTABLE('1cid des PF');'1cid des PF'[Quantité code mouvement.2])

Dép = 'Site-Article-Fournisseur'[Couverture Collisage]-1

And here is the circular dependency error message I get :

"Site-Article-Fournisseur[Couverture Collisage], Site-Article-Fournisseur[Dép], Site-Article-Fournisseur[Couverture Collisage]"

Can anyone help me one this please?

Thanks,

Antoine

[WP]

Hi Antoine,

how do the relationships look between the different tables : 'colisage', '1cid des PF' and 'Site-Article Fournisseur' Do all tables have primary keys?

Are these 3 the only tables in the model? Do you have filtering in both directions on for all relations in the model (which will lead to ambiguity in the model and circular dependencies)?

Error message says that to calculate couverture collisage, the formula is also taking Dép into account, and not only vice versa, so I think key to solution lies in looking in to the relationships between your tables.   

[AntoineB]

I'm not sure the tables have primary keys (I have not found how to manage primary key directly in PBI).

The relation between tables are the following :

Does it help to understand?

[AntoineB]

[WP]

Antoine,

Try disabling bidirectional filtering on at least one of your one2many relationships, by double clicking on relationships and direct them from 1 to many. (default)

Harder to explain why then to try 🙂

The idea goes something like : when calculating your couverture collisage, you allow filtering your S-A-F table by other tables. The moment another table filters the S-A-F table, this contains the dép column. The fact that filtering is possible either through fcid and colisage at the same time adds some ambiguity. Removing the bidirectional filter, would solve this if this is the cause.

good luck!

[AntoineB]

Thanks for for ideas but I'm afraid it don't work. Any other idea?

[WP · Accepted solution]

Antoine,

Well my next try would be to make the model less complex. E.g. the intermediaire table has a 1-1 wit the other table, maybe there is a way to combine them since the fields in both seem similar as well? 

[AntoineB]

I think the last idea worked, thanks a lot.