Use values of multi-select filter conjunctively

I am trying to get a count of users of let’s say services A,B,C,… such that a multi-select filter on services only returns the number of users who used a combination of selected services and not the number of users who used any of the selected services.For example, if we have 3 users: one used services A, B and C, another used
services B and C, and the last one used only services C. If i checked A,B and C on the multi-select filter, then
i expect a count of 1 (the user who used all the 3 services selected)
How can i achieve this in Quicksight?

Assuming your dataset looks like this:

user_id service
u1 A
u1 B
u1 C
u2 A
u2 C

The following trick might work to get what you want… It assumes that your (unfiltered) dataset contains all services you have listed in the multi-value parameter

Add a calculated field called {User has all services}.

ifelse(
  distinctCountOver(service, [user_id], PRE_AGG) = 
  distinctCountOver(
    ifelse(in(service, ${multiValueServiceParameter}), service, null),
    [], PRE_FILTER
  ), 
  'YES', 'NO'
)

Then add a filter for {User has all services} equals ‘YES’

2 Likes

Thanks for the your response @darcoli. I have adapted it to my use case and it appeared to have worked but the result did not agree with the result from a SQL query. I’m still investigating to know why the results are not the same. I’ll be back with my findings to confirm it worked. Thanks a lot.

Hi @darcoli, I finally found why the result did not agree with my SQL query; my data has date field (month) so i had to include that as a partition field. I can now confirm the trick worked, thanks a lot

1 Like

Thanks for sharing this with the community @nelsonajayi! :slight_smile: