Tableau: filtering results from 2 elements from a same dimension

Printscreen of the data and the expected output

I'm working with a large dataset, but I'm having a problem at Tableau and/or at SQL - Data comes from a SQL query.

Here's a simple example:

Let's say I have a bakery and I wanna know which ingredients I have could necessarily go well in a focaccia, but also do well in other baked goods such as "bread" or "pizza".

| Baked Good Type | Ingredients | | Focaccia | Olives | | Pizza | Olives | | Bread | Olives | | Tart | Strawberry | | Cake | Strawberry | | Focaccia | Onions | | Pizza | Onions | | Tart | Blueberry | | Cake | Blueberry |

By creating calculated fields, and/or duplicating my data source, or by doing any other type of magic, how can I display the following result?

Q. How many ingredients can we have for both Focaccia and for the following type of Baked Goods? | Baked Good Type | Count Distinct of Number of Ingredients | | Pizza | 2 | (meaning olives and onions) | Bread | 1 | (meaning olives only)

I tried duplicating my data source and doing some blending:

main data source: IIF (Baked Good Type='Focaccia', '', 'Yes') -- would identify the ingredients that can go in other things that not focaccia

secondary data source IIF (Baked Good Type='Focaccia', 'Yes', '') -- would identify the ingredients that can go in a focaccia

The calculated field in main data source is connected to the calculated field in the secondary data source (so this is the blending).

I then used the calculated field in the main data source as filter and wanted to consider the results that were '' only - which would give the the good numbers, but then the name of the other baked goods wouldn't show up.

If I then considered the 'Yes' in the calculated field of the main data source, I could see the names of the other baked goods (i.e., pizza and bread), but the results were incorrect.

Any tips? Thaaaaaaanks!!!

Sounds like you want to first filter to only look at ingredients that are associated with Foccacia, and then do your analysis on those ingredients.

To do that, drag Ingredient to the filter shelf and filter using the condition MAX([Baked Good Type] = “Foccacia”)

That will include only ingredients that have at least one row where the baked good is Foccacia. You can read MAX() of a boolean expression as “at least one” or “there exists”. That is because True is treated as greater than false.

This will include all data rows involving the ingredients that qualify — most critically, even the rows involving other baked goods.