Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |