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. 

Answer 1

• 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:

  • In the data pane, select the Code dimension 

  • Create a set based on the Code dimension

  • On the General tab of the Set definition dialog, Choose "Use All" 

  • On the Condition tab, select "By Formula" 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 shelves to combine it with other sets to form new ones or display codes in the set. 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

If you also want to learn Tableau and make your career in it then I would suggest you must take up the following Tableau Certifications course online.