FILTER /w string variable

Hi Anonymous ,

As far as I know Power BI doesn't support us to create calcualted table to show dynamic results. Here I have a workaround. I suggest you to create a 'Goods' table with all items and ingredients in 'Product' table.

Item and Ingredient = 
VAR _UNION =
    UNION (
        VALUES ( Products[Item] ),
        VALUES ( Products[Ingredient 1] ),
        VALUES ( Products[Alternative ingr 2] ),
        VALUES ( Products[Alternative ingr 3] )
    )
VAR _FILTER =
    SUMMARIZE ( FILTER ( _UNION, [Item] <> BLANK () ), [Item] )
RETURN
    _FILTER

Then create a measure to filter this table.

Filter = 
VAR _ITEM =
    SELECTEDVALUE ( Products[Item] )
VAR _Ingredient1 =
    SELECTEDVALUE ( Products[Ingredient 1] )
VAR _Ingredient2 =
    SELECTEDVALUE ( Products[Ingredient 2] )
VAR _Ingredient3 =
    SELECTEDVALUE ( Products[Ingredient 3] )
VAR _CURRENTVALUE =
    MAX ( 'Item and Ingredient'[Goods] )
RETURN
    IF (
        _CURRENTVALUE = _ITEM
            || _CURRENTVALUE = _Ingredient1
            || _CURRENTVALUE = _Ingredient2
            || _CURRENTVALUE = _Ingredient3,
        1,
        0
    )

Add this measure into visual level filter and set it to show items when value = 1.

Result is as below. When I select Rum in slicer, we can see Rum/Wood and Suger cane in table.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.