4 Replies Latest reply on Jan 26, 2018 1:14 PM by Satish C

    Need help calculating distinct count by two values, but one needs to not be counted if containing the other value.

    Bryan rose

      I have an excel file with about 1000 users that contain a users ID and a column with their Transcript Status of either "Registered" or "Waitlisted".  I want to have a calculated measure, or way, to show the distinct count for Registered users and Waitlisted users.  However, there is a chance that a Waitlisted user could also be Registered as well, and if that is the case, they should not be counted in the Waitlisted distinct number.

       

      I am still new to Tableau and have been trying to use as many options as possible, but i keep ending up with distinct number of Registered and Waitlisted users, when in reality, some of those Waitlisted users should not be counted, due to them already being Registered.

       

      Can anyone provide direction on which calculation function to use? i was thinking LOOKUP() but i still cant remove those users from Waitlisted.  In the example below, the final distinct count for Registered would be 6 while the Waitlisted distinct count would be 3 (because 11111 and 22222 are already marked as Registered.

        

      Person IDStatusCourse
      11111RegisteredCourse 1
      22222RegisteredCourse 1
      33333RegisteredCourse 1
      44444RegisteredCourse 1
      55555RegisteredCourse 1
      55555RegisteredCourse 2
      66666RegisteredCourse 1
      66666RegisteredCourse 2
      66666RegisteredCourse 3
      66666RegisteredCourse 4
      11111WaitlistedCourse 1
      22222WaitlistedCourse 1
      77777WaitlistedCourse 1
      88888WaitlistedCourse 1
      88888WaitlistedCourse 2
      99999WaitlistedCourse 1

       

      Thank you!