Tableau: Get the ids that contain only the selected values from another column

Question

I have the following question! I have a table like this:

Data Source

I want to create a field(i suppose it's a field) that i can take the apl_ids, that have as service_offered some that i want.

Example from the above table. If i want the apl_ids that have ONLY the service_offered Pending 1, Pending 2 and Pending 7.

In that case, I want to get the apl_id = "13" since apl_id = "12" got one more service that i don't need.

Which is the best way to get that?

Thank you in advance!

Answer 1

Add a calculated field which gives 1 for desired values and 0 for other values. Add another calc field with fixed LOD to apl_id to sum of calcF1. Filter all ids with values=3 only. I think that should work. Else tell me I will post screenshots

Answer 2

You can create a set based on the field api_id defined by the condition

max([service_offering]=“Pending 1”) and
max([service_offering]=“Pending 2”) and
max([service_offering]=“Pending 7”) and
min([service_offering]=“Pending 1” or [service_offering]=“Pending 2” or [service_offering]=“Pending 7”) 

This set will contain those api_ids that have at least one record where service_offering is “Pending 1” and at least one record with Pending 2 ... and where every record has a service offering of 1, 2 or 7 (I.e. no others)

The key is to realize that Tableau treats True as greater than False, so min() and max() for boolean expressions correspond to every() and any().

Once you have a set of api_ids() you can use it on shelves and in calculated fields in many different ways.