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, in the table below, I would like to know how to count the number of students who have attended a subject of Math and Science. I have tried the following approach, which instead gives me the number of students who have attended math OR science:
Math | Jess |
Math | Jess |
Math | Matt |
Math | Matt |
Math | Kyle |
Science | Kyle |
Science | Kyle |
Science | Pat |
Science | Jess |
@Anonymous
Try this;
Maths and Science =
VAR Math = CALCULATETABLE(VALUES(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = "Math"))
VAR Science = CALCULATETABLE(VALUES(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = "Science"))
RETURN
COUNTROWS(
INTERSECT(Math; Science))
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown
unforunately, it will not work because there could be more then two subjects
now, as i know, the topicstarter'sproblem is in the incorrect fields name
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38
@rajulshah's method seems correct to me, I have the similar idea. You could re-try his formula.
The idea is first count the number of subjects taken for each students, then count the number of students who took more than 1 subjects.
Count =
VAR Subjectsattend =
CALCULATE (
DISTINCTCOUNT ( Student_Table[Subject] ),
ALLEXCEPT ( Student_Table, Student_Table[Student] )
)
RETURN
IF (
Subjectsattend > 1,
COUNTX ( ALLEXCEPT ( Student_Table, Student_Table[Student] ), Subjectsattend )
)
Best,
Paul
He didn't originally ask for a solution which was to be dynamic or to cater for more subjects.
@Anonymous
If you need a solution which allows for dynamic selection, you can try this:
(I have added students and subjects to your example to illustrate the dynamic nature of the solution. I have also added a table with only the subjects to act as a slicer)
1) The subject table (which is for the slicer) has an index column which I use to create a rank with a measure. This way I can specify what needs to be calculated based on what and how many subjects are selected.
2) I then build the DAX which:
a) evaluates whether there is only 1, 2, 3 or 4 subjects selected
b) calculates the number of students depending on the number and subjects selected in the slicer.
(You will see I also created a measure to list the students studying the selected subjects, and did some conditional titles etc..)
The DAX I'm using to do create the dynamic counting is:
Dynamic Subjects calculation measure =
VAR Sub1 = CALCULATETABLE(VALUES(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = CALCULATE(MIN(Subject[Subject]); FILTER(Subject; [Rank Subjects] = 1))))
VAR Sub2 = CALCULATETABLE(VALUES(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = CALCULATE(MIN(Subject[Subject]); FILTER(Subject; [Rank Subjects] = 2))))
VAR Sub3 = CALCULATETABLE(VALUES(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = CALCULATE(MIN(Subject[Subject]); FILTER(Subject; [Rank Subjects] = 3))))
RETURN
IF(COUNTROWS(Subject) = 4;
CALCULATE(DISTINCTCOUNT(SubjectStudent[Student]);
FILTER(ALL(SubjectStudent[Student]);
[Count Subjects]= 4));
IF(COUNTROWS(Subject) = 3;
COUNTROWS(
INTERSECT(Sub3;
INTERSECT(Sub1; Sub2)));
IF(COUNTROWS(Subject) = 2;
COUNTROWS(
INTERSECT(Sub1; Sub2));
CALCULATE(DISTINCTCOUNT(SubjectStudent[Student]);
FILTER(SubjectStudent;
SubjectStudent[Subject] = SELECTEDVALUE(Subject[Subject]))))))
And here is the result:
If you're interested I can post the link to the file.
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
I've actually thought of a much simpler way of achieving what you are looking for.
This method involves:
1) counting the subjects per student which are within the same set of subjects selected in the slicer
2) comparing this number with the total number of subjects selected.
In effect we ask ourselves: which subjects does each student study which are in the selection? And then we can say that if these are equal to the total subjects selected, then we can count this student.
The model is:
To list the subjects selected is simple: (this, of course isn't actually a measure per se)
VALUES('Dim Subjects'[Subject])
To count these subjects:
Count Rows selected Subjects = COUNTROWS(VALUES('Dim Subjects'[Subject])
Now to count the number of subjects that each student lists and which are within the set of subjects in the "slicer":
Count Rows of sudents with same subjects as selected =
COUNTROWS(
DISTINCT(
FILTER(SubjectStudent;
SubjectStudent[Subject] IN VALUES('Dim Subjects'[Subject]))))
With this we can get:
If we then compare this measure to the total number of subjects selected in the slicer we can count the students which list the subjects selected:
Filter Students with AT LEAST selected subjects =
IF([Count Rows of sudents with same subjects as selected]>= [Count Rows selected Subjects]; [Count Rows of sudents with same subjects as selected])
And to count the actual students who meet the criteria:
Students with AT LEAST selected subjects =
CALCULATE(
DISTINCTCOUNT('Dim Students'[Student]);
FILTER('Dim Students';
[Count Rows of sudents with same subjects as selected]>= [Count Rows selected Subjects]))
And from there we can do the same for those students who only list all the subjects selected.
Filter Students with ONLY selected subjects =
CALCULATE(DISTINCTCOUNT('Dim Students'[Student]);
FILTER('Dim Students';
[Count Rows of sudents with same subjects as selected] = [Count Rows selected Subjects]
&&
[Count Rows of sudents with same subjects as selected] = [Number Subjects (all) by Students]))
and to count the students who only list the selected subjects:
Students with ONLY selected subjects =
CALCULATE(
DISTINCTCOUNT('Dim Students'[Student]);
FILTER('Dim Students';
[Filter Students with ONLY selected subjects]>0))
To end up with:
Proud to be a Super User!
Paul on Linkedin.
Hello @Anonymous ,
Please try the following dax function:
Measure = COUNTX(
FILTER (
SUMMARIZE (
Student_Table,
Student_Table[Student],
"Total Subjects", DISTINCTCOUNT( Student_Table[Subject] )
),
[Total Subjects] > 1
),Student_Table[Student]
)
Hope this helps.
Hi @rajulshah , its unfortunately returning a (Blank) when I drop this measurement into a visual.
@Anonymous , can you provide the file? Because I tried with the same data and it works.
Hello @Anonymous ,
If the solution helped you, then please mark the post as Solution so that it is easy for others in need. 🙂
Hi @Anonymous
try a measure
Measure = calculate(distinctcount('Student_Table'[Student]);FILTER(summarize(filter(distinct(ALL('Student_Table'));'Student_Table'[Subject] = "Math" || 'Student_Table'[Subject] = "Science");Student_Table[Student];"countrows";DISTINCTCOUNT('Student_Table'[Subject]));[countrows]=2))
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @az38 ,
Thanks for your effort, but your solution unfortunately returns a (Blank) when I drop the measurement into a visual. Any different approach?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |