Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Hi,
Try these measures
Student count = distinctcount('Table'[Username])
Schools with >20 students = countrows(filter(values('Table'[ClassName]),[Student count]>20))
Hope this helps.
Really helpful, thanks so much Ashish_Mathur!
You are welcome.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |