find number of times distinct people linked to a group appear in a day

[Anonymous]

date	group	person number	desired result
01-Jan	A	a	2
01-Jan	A	b	2
01-Jan	A	a	2
01-Jan	B	c	1
01-Jan	B	c	1
01-Jan	B	c	1
02-Jan	A	d	3
02-Jan	A	e	3
02-Jan	A	f	3
02-Jan	B	g	2
02-Jan	B	h	2

I have a group, a date and some people. Each group can have associated to it multiple people (sometimes with repetition). I want the returned value to be the number of distinct people associated to a group on a specific day. Using the example above, on 01/01, "A" had 2 distinct people (as person "a" is repeated). I tried using count, filter and calculate however I am very new to PBI so I have gotten nowhere thus far.

Thank you in advance.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a column as below.

Column = CALCULATE(DISTINCTCOUNT(Table1[person number]),ALLEXCEPT(Table1,Table1[date],Table1[group]))

Regards,