Count instances where ID occurs less than N times in column

[lightw0rks]

I have data similar to the following:

ID	Date
A	01/01/2020
A	02/01/2020
A	03/01/2020
B	04/01/2020
B	05/01/2020
C	06/01/2020

How can I count the number of instances a unique ID appears less than 3 times.

E.g. both B and C occur less than 3 times in the ID column, A does not, so the expected result would be '2'.

[amitchandak · Accepted solution]

@lightw0rks , Try a measure like

Measure =
var _instance =3
return
countx(filter(summarize(Table,table[ID],"_1" ,count(table[Date])),[_1]<_instance),[ID])

_instance can come from a what if parameter

[lightw0rks]

Thanks guys, both those solutions work!

[harshnathani · Accepted solution]

Hi @lightw0rks ,

Try this measure

Measure 3 = 

var _table = 

SUMMARIZE('Table','Table'[ID], "_1", COUNTA('Table'[ID]))
RETURN
COUNTROWS(FILTER(_table, [_1] >=2))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak · Accepted solution]

@lightw0rks , Try a measure like

Measure =
var _instance =3
return
countx(filter(summarize(Table,table[ID],"_1" ,count(table[Date])),[_1]<_instance),[ID])

_instance can come from a what if parameter