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.

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 ID Status Course 11111 Registered Course 1 22222 Registered Course 1 33333 Registered Course 1 44444 Registered Course 1 55555 Registered Course 1 55555 Registered Course 2 66666 Registered Course 1 66666 Registered Course 2 66666 Registered Course 3 66666 Registered Course 4 11111 Waitlisted Course 1 22222 Waitlisted Course 1 77777 Waitlisted Course 1 88888 Waitlisted Course 1 88888 Waitlisted Course 2 99999 Waitlisted Course 1

Thank you!

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

Used a set.

see sheet 2

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

Thanks Satish! It looks like this is producing the correct info, but i am confused exactly HOW this works.  I am looking at the calculation and you have:

IF [Count Set]  and [Status] = 'Waitlisted' then 'Waitlist' else 'Registered' end

Can you help explain how that works?  I am still new to tableau and calculations and i want to make sure I am comprehending all calculations so i can become proficient in writing these myself

Also, why did you include "then 'waitlist'"  There is no 'waitlist' status in the data.

Tx

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

Hi Bryan,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread

1. D1. End Status:  if  ({fixed [Person ID],[Course]:max([Status])})=({fixed [Person ID],[Course]:min([Status])}) then  [Status] else "Registered" end

2. M1. Counter: countd([Person ID])

3. Drag the required objects to the indicated locations.

Regards,

Norbert

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

The calculation means you are finding the person ids with a status distinct count of 1 (thats what the set is doing - pulling all person ids with only a single status into it ) and then equating it to waitlisted.

The then part of an if condition can be anything you write.It creates a new column in the data. It doesnot need to be in the data. Lets say you have a height field and you could write if height>6 feet then 'tall' else 'not tall' tall and not tall do not need to be in the data.

Does this help?