Tableau: creating calculated field based on cell values

Question

Say I have the following worksheet table (the measure is an aggregate sum):

    Code    A   B
     123        10
     456    20  30
     789    40  

I'd like to create a calculated field (dimension) that I could use to filter out both the first and third row, in other words, keep only that record where A and B are both positive for the given Code. I've tried IF/ELSE with sum(measure) > 0 in the conditions but apparently I can't mix measures and aggregates. Thanks for any pointers.

Solution

One solution is to create a dynamically computed set based on the Code dimension that only includes codes that meet your criteria. You can do that by:

• Selecting the Code dimension in the data pane
• Create a set based on the Code dimension
• Choosing "Use All" on the General tab of the Set definition dialog
• Selecting "By Formula" on the Condition tab and entering the formula Sum(A) > 0 and Sum(B) > 0

Once you have defined your set, you can place it on the filter shelf to reduce the data used in the visualization, or use it on other shelfs to display codes in the set, or combine it with other sets to form new ones. You can also refer to sets by name in calculated fields to test membership.

The set above has the same effect as the following SQL:

select Code, ... from ... group by Code having sum(A) > 0 and sum(B) > 0