Forum Discussion
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 example, if a user selects "Apple", they will see a quantity of 4 or if a user selects "Jalepeno", they will see a quantity of 9
This was handled using a single ingredient column as such:
Ingredients =
DISTINCT (
UNION (
VALUES ( Recipes[Ingredient 1] ),
VALUES ( Recipes[Ingredient 2] )
)
)
and then applying the following measure:
Total Qty :=
VAR i =
SELECTEDVALUE ( Ingredients[Ingredient] )
RETURN
CALCULATE ( SUM ( Recipes[Qty Ingredient 1] ), Recipes[Ingredient 1] = i )
+ CALCULATE ( SUM ( Recipes[Qty Ingredient 2] ), Recipes[Ingredient 2] = i )
This works exactly as designed (thanks DataNinja777 !) However, my question here is how do I create a measure that will generate values if there are multiple selections - i.e. if a user selects "Apple" AND "Jalepeno" they will see a quantity of 13. Or if nothing is selected, they will see the sum of quantities on the entire table.
thanks!
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.
7 Replies
You are welcome! Happy that it helps!
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.
