How to Create measure count from Group ID if it has at least one appearance of a specific value?

[user35131]

ID	Col 2
1	N
1	N
1	Y
2	N
2	N
3	Y
3	Y
3	Y
4	N
4	N
4	Y
4	N
5	N

Note that Group ID 1 has at least one "Y" so count 1. Group ID does not have a "Y" so no count. 3 has all "Y" so count 1. 4 has all N but at least one "Y" so Count 1. 5 has no "Y" so no count. 

In total my measure should provide me a count of 3. 

[v-yangliu-msft · Accepted solution]

Hi  @user35131 ,

Here are the steps you can follow：

1. Create measure.

Count Group ID =
var _table=SUMMARIZE('Table','Table'[ID],"1",CALCULATE(COUNT('Table'[Col 2]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Col 2]="Y")))
return
COUNTX(FILTER(_table,[1]>=1),[ID])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[user35131]

I'm also getting the unique count without the condition if it has more than one distinct value.

[TheoC]

Hi @user35131 

You can use the following measure to achieve the output you want.

Measure = 

VAR _1 = COUNTROWS ( FILTER ( DISTINCT ('Table'[ID] ) , DISTINCTCOUNT ('Table'[Col 2] ) > 1 ) )

RETURN 

_1

Hope this helps and all the best!

Theo 🙂

[user35131]

Even though it doesn't reference the "Y" in the code it will work? 

[TheoC]

@user35131 it returns anything with more than one distinct value. Give it a go and let me know how it goes 🙂

[user35131]

I'm getting the unique count without the condition that there is at least one distinct count.

[TheoC]

@user35131 Did you add Client ID and then the measure to a table?