Forum Discussion
Creating a measure that can filter multiple values from multiple columns
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.
I suggest using PowerQuery to restructure your data first before building your visuals. In PowerQuery, unpivot your columns to create a tidy table with the following fields(or do it manually in Excel):
Ingredient
Ingredient Sequence (e.g., 1 or 2, based on the original column)
Quantity
Quantity Type (e.g., "Qty 1" or "Qty 2")
Once you've transformed the data, load it into your data model. Then, in your report, create a table or bar chart using the Ingredient field and Quantity (with aggregation set to SUM). This will automatically show total quantities per ingredient — even when multiple ingredients are selected.
If needed you can also create a card visual and drag the Quantity field into it (set to SUM). This will show the total quantity across all selected ingredients. Whether users select ingredients from the table, bar chart, or a slicer, the total in the card will dynamically adjust based on the selection.
Hope this helps!
Hi Arash, thank you so much for your quick response. I started going down the unpivot columns path and may revert back to it if absolutely necessary. The problem I was running into there was due to perfomance issues. The example I gave above is extremely simplified. I am working with millions of rows of data and when I begin factoring in customer, region, month, etc, the dataset start compounding rapidly.
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.
Thanks again! I hadn't thought of that approach and it just might work. I'll try it and test it when I return to work in the morning and will let you know. I really appreciate it.
