Nested filters

Community Discussions
, , ,
1

I am attempting to perform a multi-step query according to the following steps:

  1. Query a table to find the events (and which group) a certain person played in. My basic table would have three columns - Event, Player, Group
  2. Requery the table to find which players played in the same event and the same group
  3. Count the total number of times an individual has played with another person across all events

Any thoughts?

Thanks,
Jeff

3

Hi @Jeff-2aca thanks for your question and welcome to the community!

On your question, it is certainly doable. Without knowing the specifics of the construction of your workbook though, it is hard to say exactly how.

Here's one way I can think of how it would work. I assume in your workbook that the Event and Group columns are picklists. I would then add an Event_Group column in the table that somehow creates a unique value for each unique combination of event and group, say by concatenating the two the first column from the source table of each picklist. This helps us find the unique combination of events and groups you're looking for in the step 2. Then for your question #1, assuming that you're querying the table from an app screen for the user who is logged on, the filter might look something like:

=Filter(GamesTable, "GamesTable[Player]=$[SYS_USER]")

Let's assume the above filter is saved in a datacell in the screen called Games. To look for all players who have been in the same events and groups combination as this user we could use a filter like so:

=Filter(GamesTable, "GamesTable[Event_Group] IN $[Games][Event_Group]")

Let's assume the filter above is saved in a datacell called Users Event_Groups. Finally to count all the people the user has played with, I will assume that the Player column too is a picklist on a Players table, then we can get that by extending the query above one more level by doing something like:

=Filter(Players, "Players IN $[Users Event_Groups][Player]")

There are other ways to achieve the same result if your tables are laid out differently than I assume here, but I hope this gives you an idea! Let us know if this helps.

5

Thank you!