Find the distinct count of a measure based on another

[KAmorris]

Hi! I'm a PowerBI newbie and still learning. I'd appreciate any help you could provide!

I have a table with the following data:

School	ClassName	Username
Text	Text	Text

I'm trying to find out how many classes have 20 or more students in them. 

I have tried to use the following measure:
# classes >20 students = SUMX(
Values('Table'[ClassName]), 
IF (
CALCULATE (DISTINCTCOUNT ('Table'[Username]))>=20,
1,0

It seemed to work except when I looked closer, it said that there was 1 class (across all of the schools) with 20+ students, yet looking at the school-specific data, there were no classes with 20+students.

Another idea I was considering was whether a calculated column would make more sense...something that would show up as a yes/no whether a student is in a class of 20+ students, but wasn't sure where to start. Nor was I sure how to then use that column to find the # of classes that have 20+ students in them. 

Any advice/refinements would be much appreciated!

Thanks so much in advance!
  

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Student count = distinctcount('Table'[Username])

Schools with >20 students = countrows(filter(values('Table'[ClassName]),[Student count]>20))

Hope this helps.

[KAmorris]

Really helpful, thanks so much Ashish_Mathur!

[Ashish_Mathur]

You are welcome.

[lbendlin]

A measure can be impacted by user filter choices. A calculated  column is immutable.  Can your class size be impacted by a filter choice?

[KAmorris]

Really helpful question to think through, thanks! Class size wouldn't have been impacted by a filter choice so perhaps a column would have been okay.