How to count values that satisfy multiple conditions in a column

[Anonymous]

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:

CALCULATE(DISTINCTCOUNT('Student_Table'[Student]),filter('Student_Table','Student_Table'[Subject] = "Math" || 'Student_Table'[Topic] = "Science")). This will give a count of four because four students have attended math or science subjects, but I need it to return two, as only Jess and Kyle have attended BOTH math AND science.

Subject   Student

Math	Jess
Math	Jess
Math	Matt
Math	Matt
Math	Kyle
Science	Kyle
Science	Kyle
Science	Pat
Science	Jess

[PaulDBrown]

@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))

[az38]

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

[V-pazhen-msft]

@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

[PaulDBrown]

@az38 

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.

[PaulDBrown]

@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:

[rajulshah]

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.

[Anonymous]

Hi @rajulshah , its unfortunately returning a (Blank) when I drop this measurement into a visual. 

[rajulshah]

@Anonymous , can you provide the file? Because I tried with the same data and it works.

[rajulshah]

Hello @Anonymous ,

If the solution helped you, then please mark the post as Solution so that it is easy for others in need. 🙂

[az38]

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

[Anonymous]

Hi @az38 ,

Thanks for your effort, but your solution unfortunately returns a (Blank) when I drop the measurement into a visual. Any different approach?

[az38]

@Anonymous
For me it works with your data. Pay attention in your initial sentence there are two columns: subject and topic