cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KAmorris
Frequent Visitor

Find the distinct count of a measure based on another

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:

SchoolClassNameUsername
TextTextText

 

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!
  

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try these measures

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

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Try these measures

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

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Really helpful, thanks so much Ashish_Mathur!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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

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. 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.