Creating a Measure that can look for a value that exists in more than 1 column

[krisplikj]

Hello, I am trying to create a measure that allows a user to select an ingredient and then take the sum of the quantities, regardless of whether they fall in Qty Ingredient 1 or Qty Ingredient 2 columns.  For example, if a user selects Apple, a value of 4 is returned; or, if a user selcts Jalepeno, a value of 9 is returned.  Here's the catch... this is a partial list of ingredients.  There are far too many to create a measure for each one, so the one desired measure would display the result of any ingredient the user selects.  Thank you!

[DataNinja777 · Accepted solution]

Hi @krisplikj ,

Create a single-column ingredient dimension so the slicer has one field to filter on. In DAX:

Ingredients =
DISTINCT (
        UNION (
                VALUES ( Recipes[Ingredient 1] ),
                VALUES ( Recipes[Ingredient 2] )
        )
)

Put Ingredients[Ingredient] on your slicer. Then one measure can grab the selected ingredient and add up the matching quantities from both columns:

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 )

When the user chooses “Apple,” the first branch contributes 3 and the second contributes 1, so the measure shows 4; selecting “Jalepeno” returns 9 by summing 6 and 3.

Best regards,

[krisplikj]

Hello Data Ninja, I was able to get this to work creating a single-column ingredient column as a new table and have accepted the solution as it is working exactly the way I spelled out above.  Many thanks and kudos to you!!  I now need to know how to generate values when more than one are selected (or none selected).  I will post this question in a seperate thread unless you'd like to take a stab at it here.  Thanks again!

[krisplikj]

Hi Data Ninja, thanks for the quick reply!  I think this will work, however I am getting "a table of multiple values was supplied where a single value was expected" message when trying to create the single-column ingredient dimension.  Do you have any idea how I can adjust the syntax to address this?