Find dimension with only a single row that has a specific value in Tableau

Question

My dataset looks like

0012    Apple
0012    Multi
0012    Orange
0012    Banana
0014    Apple
0014    Multi
0014    Orange
0025    Multi
0021    Multi
0032    Apple
0032    Multi
0032    Orange
0032    Banana
0034    Apple
0034    Multi
0035    Apple

In tableau I have the follwing view

and what I try to achieve is a column that only marks "yes" for the stores that has only one single row, and the value for the row is "Multi". So In my data, only store 0021 and 0025 should have "yes". All other rows should have "Null". So when I filter I should only see the yellow rows in the picture above.

I have tried with the following calculations but it doesn't give me the expected result.

Calculation1 = { FIXED [Store]: MAX([Product]) = "Multi"}

and

Calculation2 = IF [Product] = "Multi" THEN "Yes" END

Any ideas how I can achieve this?

Answer 1

You can achieve this using 2 calculated fields:

Calculation 1: {FIXED [Store]: MAX(IF Product = "Multi" THEN 1 ELSE 0 END)}

Calculation 2: {FIXED [Store]: IF MAX([Is Multi])=1 THEN 'Yes' END}

Answer 2

One way I found to solve this is the following:

Calculation 2: IF [Product] = "Multi" THEN "Yes" END.

This part sets the value "Yes" if the value "Multi" exists.

Calculation 4: {FIXED [Store]: MAX(IF ([Product] <> "" AND ISNULL([Calculation2])) THEN "No" END)}

This part aggregate the Null values from Calculation2. If a store number has one value that is "Null" in Calculation2 then all the rows for the store will get "No". For the remaining store it will show "Null".

Calculation 5: IFNULL({FIXED [Store]: MAX(IF ([Product] <> "" AND ISNULL([Calculation2])) THEN "No" END)},"Yes")

To show "Yes" for the remaining stores that got "Null" we wrap it in a "IFNULL".

Final result is: