Count and filter multiple row values in one column with same unique ID in another column

I am having some difficulty effectively reporting (counting, filtering, etc) on unique IDs that have data listed in multiple rows in another column within a dataset.

Ex, my data is structured like this:

ServiceName EntityID

A 00001

B 00001

C 00001

A 00002

B 00002

C 00002

A 00003

B 00003

A 00004

C 00005

etc...

I need to be able to determine, based on one of the ServiceName values what other service names exist for that entity ID. E.x. If `ServiceName` = 'C' Exists for an EntityID, show all other ServiceNames for those EntityIDs. I would also like to be able to filter on just a few ServiceName criteria like "Show only EntityIDs where BOTH ServiceName 'A' AND 'C' exist".

Example expected output in a filtered table for :

ServiceName EntityID

A 00001

B 00001

C 00001

A 00002

B 00002

C 00002

C 00005

Answers

  • I think that you would need to restructure your data. If you had something like this:

    Then you could create some filter cards for each of the ServiceNames. If you wanted only entities with both service A and C, then you would filter for `ServiceName A` = 'x' and `ServiceName C` = 'x'


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman