Circular Dependency

[jamuka]

Hello,

I tried to create a stock report, based on stock quantity and forecast amount on Store/Warehouse level.

Numbers should change based on selection, e.g product x could have an 1000 total forecast but in warehouse1 it can be 100 and I want other related columns changed with this selection.

I create a measure for Stock / Days amount and tried to create calculated columns for distribution of this amount based on several conditions. So I can slice my stock amount based on product availability levels (Stock Qty / Forecast Qty) e.g "Out of Stock", "15-30 Days" etc. and it will changes accordingly.

But I get an error message  says "A circular dependency was detected"

I found that root of the problem is calculated tables but I don't have any in my report. Also I assume if I use measures instead of calculated columns there will be no problem. But I don't know how to do it.

Also I get an different error I tried to count my products distribition therefore I want to use distinct count but again I get an error.

"The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

attached you can see my file Stock Report 

kind regards

[v-diye-msft]

Hi @jamuka 

This is a good refresher on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

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.

[jamuka]

Hi @v-diye-msft,

I read it but all I understand from it is, that I can not use a measure in a calculated colum, but didn't understand how to avoid it.

I'm trying to create a new table and will make my measure in it, then make my calculated columns in current table, maybe it will work.

thanks

[v-diye-msft]

Hi @jamuka 

If you'd like to avoid the Circular Dependency error, please :

1. Use measure instead of a calculated column.

2. If the calculated column is bound needed, please create it in power query using M code. 

3. Create a NEW table that manages the relationship between them, that not all calculated column fetches the data in the same table.

BTW, there no [Days] column in your sample that results in many formula errors. 

[jamuka]

Hi @v-diye-msft,

yes you are right about [Days] column, before I upload my sample, I simplfied and translate it ( I forgot to translate Days Column) normally it didn't cause a problem. I updated my file thank you.

I create a new table with product and Stock/Days column but because there's no location information in my new table I can't slice it correctly.

about your soluitions;

2 - I don't need to use calculated columns, my power bi knowledge is limited therefore I use it.

1 - I don't know how to use "IF" formulas in measures (e.g IF (Table[ColumnName] = "x")

3 - I don't understand how to do it?

kind regards