Count Distinct using multiple columns

Looker Q&A Looker Studio
1

I have a scenario where there are multiple IDs in a column (uniqueid) of data in a Google Spreadsheet, some IDs are also duplicated in different rows.

Some of these ids are labeled as left’, ‘right’ or ‘both’ in another column (obs).

uniqueid obs
m1 right
m2 left
m1 both
m3 right
m4 left

I want to be able to create a counting field that indicates the result of the sum that totals the highest number of distinct ids between only ‘left’ + has ‘both’ (m2+m4+m1) or only ‘right’ + has ‘both’ (m3+m1), in this case it should appear = 3. In case of equal sums between these two conditions, show the sum of only ‘right’ + has ‘both’

I tried looking for help on the forum but I can’t adapt the conditional counting formulas using two columns.

2

I think I got this to work in Looker Studio! I used several fields, but you could do it with as little as 1 if you like.

The basic logic can be summed up in this IF statement:

if([count distinct ids with right or both] > [count distinct ids with left or both] , [count distinct ids with right or both] , [count distinct ids with left or both] )

In other words, return the larger of the 2 count distincts.

To compute a count distinct that only looks at right or both, use an IF inside of a count distinct:

COUNT_DISTINCT(IF([obs]="right" OR [obs]="both",[uniqueid],null))

I hope this helps! Happy to clarify any part of this if you’d like.

1 Like
3

According to this logic it worked, thank you

1 Like