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’
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