2 Replies Latest reply on Jan 4, 2019 12:08 PM by Megan Van Dorn

    Calculated field to identify related/spouse records in a dataset?

    Megan Van Dorn

      Greetings, people of Tableau! I'm hoping someone can help me figure this out. I'm still sort of a newbie to calculated fields, so I'm not even sure if what I'm trying to do can be done. For reference, I'm currently working in Tableau Desktop 10.5.11.


      The dataset I'm using for my dashboard is a list of people who were queried from our internal database. Each record (person) in my dataset has a field that contains a unique ID number. Each record also has a field called "Spouse ID", which contains the unique ID number for the person's spouse. Since not everyone in our database is married, not every record will have a Spouse ID.


      Is it possible to create a calculated field that will tell me if the number in the Spouse ID field on one record also shows up in the unique ID field for another record? (That is, I'd want a calculated field that indicates if both Jane Doe #12345 and her husband John Doe #67890 each have a record in this dataset.)


      I know how to do this in Excel with a formula like the following, where column B contains the unique IDs and column W the Spouse IDs: =COUNTIF($B$2:$B$201,W2)>0. In Excel, this formula returns a TRUE/FALSE value. However, we will be moving to a dedicated dataset in Tableau Server shortly, and my understanding is that exporting to Excel to add more fields with formulas will not be a workable option at that point.


      Thanks in advance!