Count Distinct using multiple columns

[isabellaferreir]

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.

[sam8 · Accepted solution]

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.

[isabellaferreir]

According to this logic it worked, thank you