Not sure how to approach a slightly complex filter problem...

Looker Q&A Looker Studio
1

Hello,

I have a problem that I thought was reltively straight forward (as it is simple in Google Sheets) but is proving tricky. I have provided a simplified illustration of some data I’m working with where scores have been logged for a number of events. I want to be able to filter to just events where all people participated. In the example below the number of people is 3 but my issue is that this number could change.

Date EventID Person Score IsFullParticipation?
01/01/2025 1 Person 1 8 TRUE
01/01/2025 1 Person 2 5 TRUE
01/01/2025 1 Person 3 7 TRUE
02/01/2025 2 Person 1 3 TRUE
02/01/2025 2 Person 2 4 TRUE
02/01/2025 2 Person 3 3 TRUE
03/01/2025 3 Person 1 2 TRUE
03/01/2025 3 Person 2 3 TRUE
03/01/2025 3 Person 3 5 TRUE
04/01/2025 4 Person 1 6 TRUE
04/01/2025 4 Person 2 7 TRUE
04/01/2025 4 Person 3 5 TRUE
05/01/2025 5 Person 1 2 TRUE
05/01/2025 5 Person 2 3 TRUE
05/01/2025 5 Person 3 4 TRUE

In Google Sheets this is simple as I’m using a ‘IsFullParticipation’ Boolean column involving ‘COUNTUNIQUE’ in the formula to calculcate the number of people involved in the ‘Person’ column. In the example below the number of people is 3 but if a row were added with ‘Person 4’, then that’s fine becuase my COUNTUNIQUE formula will work out that people is now 4. The full formula used in the IsFullParticipation column is:

=COUNTIF(Sample[EventID],B2)=COUNTUNIQUE(Sample[Person])

So for reasons I won’t go into, my source data is essentially as above but does not include the ‘IsFullParticipation’ column. In Looker Studio I’m trying to find a way to filter to where all participants were involved so I’m assuming a calculated column that recreates the ‘IsFullPartipation’ column as above if the key but I am struggling. I’ve tried a COUNTDISTINCT formula on the Person column but I’m either getting errors or it’s always returning 1 rather than 3 which makes we wonder if my approach is completely wrong here.

I feel like i’m missing something or overcomplicating this! Thanks for reading and appreciate any advice!

2

This is a tricky one! As you’ve noticed, all calculated fields are computed at the row-level, rather than across the whole table. This is why you’re seeing that a COUNT DISTINCT for a given Person is 1 - because there is only one person per row.

Try removing the Person field from the table to change the grouping. Once you do that, you’ll be able to correctly compute the COUNT DISTINCT of people per event, and you should start seeing numbers like 3 and 4 as you expect.

1 Like
3

Thanks for taking the time to reply. The problem is, if I remove the ‘Person’ field then I can’t automatically calculate the number of players. Since posting this I’ve realised that I should probably have a separate Person table(?).

4

Right…you need to compute the total number of unique people, without any grouping, and then compare that the to the number of unique people grouped by event. In Looker, you can reference the total values in a table calculation, but I’m at a bit of a loss for how to do this in Looker Studio.

I think going down to the data source route makes sense - you could either include the IsFullParticipation? field in the sheet, or make a new table that computes the total number of people and then join that in using a blend.

1 Like