Forum Discussion
Power BI Table Visual pulling data that's not actually connected to the row item...
I have data tables set up the following way:
[Recipe Table] * <-> 1 [Ingredient Table] 1 -> * [Ingredient Conversion Table] 1 -> * [Data Table]
The data I need to pull lives in the data table but uses a different ingredient code convention than the recipe table so the conversion table connects that. The Ingredient table is in place to prevent the many-to-many between the Conversion and Recipe table.
I would like to display a table on my page that shows the list of ingredients for a specific selected formula and each of those ingredients' selected "Data point" from the Data Table. I'm not using measures for this. I'm just pulling in the columns from my data tables. I'm using Average for the Data Point numbers because the same ingredient may be listed multiple times (with different ingredient numbers becasue of different forms/suppliers/locations/etc.). The data point number will be identical for every instance of that ingredient, though, so average lets me return a single, correct, value.
| Ingredient (from Recipe Table) | % in Formula (from Recipe Table) | Avg of Data Point (from Data Table) |
| Water | 25% | 5.2 (???) -> should be blank! |
| Flour | 10% | 1.7 (correct) |
| Eggs | 2% | 2.3 (correct) |
This works great for 99% of my ingredients. Water, however, does not exist in the Data Table. There is no number associated with it. As a result, I expected the "Data Point" column to show a "blank". Instead, it's showing a randon number. When I switch the "avg" on that column to sum, min, max, count, etc., I can tell that it's pulling in quite a few different numbers. My coworker suggested that since Power BI can't find "water", it's just returning the average of all of the other ingredients that aren't in my table. I don't understand why it would do this instead of returning a blank.
I have only noticed this for water so far because I do not expect water to have a number. My short term solution was just to filter water out of the table (though I would prefer to show water since it's recipe contribution is still valuable) It is possible, though, that we could add additional ingredients in the future before a data point number is added, and thus, this could happen to other ingredients. In that case, I definitely want it to show a blank. I assume I can create a measure instead of using the data table column and have it show the average of the column except when the ingredient doesn't exist? But I'd really like to understand why this behavior is happening.
Thank you!
2 Replies
Your data model looks slightly suspicious. Can you refactor it into a proper dimensions/facts model?
Why does water not have a number? Milliliters? Or is it one of these infuriating recipes that says "add water as needed" ?
None of the tables are my own. I'm pulling from other sources. I can take a look at the middle tables though, and see if I can set those up as dimension tables.
The data point table is just a generic example. I guess it would technically be zero for my purposes, but it doesn't have a line item in the original data table (pulled from a source I do not own). Since it could theoretically be possible for other ingredients not to exist in that table I would rather understand why it is acting this way (vs returning a blank) than to just add a condition to set it to zero.
