Forum Discussion
Solved
Creating a measure that can filter multiple values from multiple columns
Hello PBI enthusiasts, I recently posted to create a measure that correctly sum the quantities of an ingredient, regardless of whether they fell in the ingredient 1 or ingredient 2 column. For exa...
You're welcome! I might not be able to give the perfect answer without knowing the full structure of your table and its granularity, but if you only have two ingredients, here's a suggestion:
Import your data twice — in the first query, remove Ingredient 2 and Qty 2; in the second, remove Ingredient 1 and Qty 1. (Note: if you have more than two ingredients, this approach won't scale well.)
Then, rename Ingredient 1 and Ingredient 2 to a common column name like Ingredient, and do the same for Qty 1 and Qty 2, renaming both to Qty.
Finally, append the two queries. This functions like an unpivot, but will be faster at runtime since it requires less compute once the data is loaded into the model.
