Measure counting lines where only one specific criteria is met

[julienvdc]

Hey there,

I am struggling to find the right measure that would find out the amount of users who only belong to one specific group.

I have the following table, which says to which groups users belong to:

Users can belong to multiple groups. I want to calculate how many of these users ONLY belong to group with ID 3. As you can see in my screenshot above, the total would be 3 (user ID 1235, 1236, 1237 are the only ones who solely belongs to group ID 3).

Any ideas how I could achieve this? 🙂

[SpartaBI · Accepted solution]

@julienvdc in case I understood what you want :)) try this:

Replace 'Table_Name' with the name of your table

COUNTROWS(
FILTER(
	VALUES('Table_Name'[c_members]),
	CALCULATE( NOT ISEMPTY('Table_Name'), 'Table_Name'[c_groups] = 3)
	&& CALCULATE( ISEMPTY('Table_Name'), 'Table_Name'[c_groups] <> 3)
)
)

[julienvdc]

Hey @SpartaBI !

**bleep**! I spent the whole morning trying to figure this out and you solve it in a few sec.! 

Amazing, this does the trick.

Thanks you so much!

J.

[SpartaBI]

@julienvdc Haha my pleasure 🙂
Listem, check out my showcase report, maybe you'll find there cool stuff you will appreciate 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/Contoso-by-SpartaBI/td-p/2449543