Need Assistance with DAX Measure: Filtering Students by Selected Classes

[Mr_Robot0092]

Hello,

I have 2 tables Student and Class , a student can have multiple Class then i create a table called Enrollement to manage the many to many.

As bellow a simple of the data : 

Student:

ID	Name
1	Samuel
2	Merida
3	Ajay
4	Patrick
5	John
6	Fabio

Class : 

ID	Class
1	Math
2	German
3	English
4	Computer

Enrollment : 

Student ID	Class ID
1	3
1	4
2	1
2	2
3	1
3	2
3	3
4	1
5	2
6	1
6	2
6	3
6	4

I have a measure called CntStudent 

CntStudent = COUNTROWS(Student)

In my report i have a filter with the class name and the table that show me the Class with the CntStudent

When i filter in computer i have 2 on CntStudent , it's correct like answer .

But in my case i want to get only the value 1 in CntStudent (take only the count Student that is filtered) , in my example  i filtred on Computer i have 2 students ( Id  1&6) have the computer class but i want to eliminate the count of student 1 because he has 2 classes (Computer and English).

Another example , i filtered on class Math & German , the result show me 4 students for each Class .

In my case i want to retrive 2 students for each Class .(Count only Student ID 2,4 and 5)

Explination : I want to eleminate all the students who have more classes and non selected on filter , for example for the student ID 6 have 4 classes (Math,German , English & Computer ) the count of this student don't need be included because i filtered only on (Math & German) 

Any idea how can i do that ?

Thanks for help ! 

[Ashish_Mathur]

Hi,

Your logic is not clear.  Looks like you want to count students who have take only the selected subject(s).  If that be the case, then when you select only Computer in the subject slicer, why should even student 6 be considered (Student 6 has taken 4 subjects).  Please clarify.

[Mr_Robot0092]

Hello @Ashish_Mathur ,

Thanks for replying.

I want to create a measure that counts only the students who are enrolled in the selected classes and have not been enrolled in any other classes outside of the selected ones.

Example : 

 i filtered on class Math & German , the result show me 4 students for each Class . As bellow the selected value : 

Merida	Math
Merida	German

Ajay	Math
Ajay	German

Patrick	Math

John	German

Fabio	Math
Fabio	German

In my case i want to retrive 2 students for each Class .(Count only Student Merida,Patrick and John)

Why ?

The student Ajay and Fabio have more than 2 classes that was filtered  : 

Ajay	Math
Ajay	German
Ajay	English

Fabio	Math
Fabio	German
Fabio	English
Fabio	Computer

 I want to remove all the students who have more classes and non selected on filter.

Hope is clear 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mr_Robot0092]

Hello @Ashish_Mathur 

Thanks for replying.

Your solution doesn't answer to my question.

The idea is display a table with Class and Cout of student Like as bellow : 

i filtered on class Math & German : 

Class	CountStudent
MAth	2
German	2

Thanks for your help !

[Ashish_Mathur]

Hi,

Shouldn't the answer be 3?  John, Merida and Patrick.  Each one of them has take either only German or only Math or only Both German and Math

[Mr_Robot0092]

Hi @Ashish_Mathur ,

Sure John, Merida and Patrick are the good answer.

But in my case i have a table with a Class dimension and count Student the result will be like as bellow : 

Class	CountStudent
Math	2
German	2

[Ashish_Mathur]

I am completely confused.  On one hand you so that the answer should be 3 but in the table you show 2.

[Mr_Robot0092]

@Ashish_Mathur 

in my case i select Class with the Count Student 

[amitchandak]

@Mr_Robot0092 , Try like

M1 = distinctcount(Table[Subject])

M2 = calculate(distinctcount(Table[Subject]), removefilters(Table[Subject]) )

Student with allselected = countX(Values(Table[Student]), if([M1] = [M2], [Student], blank()) )

or refer, if needed

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk

[Mr_Robot0092]

Hello @amitchandak ,

thanks for replying.

Doesn't give me the right result like i want . I want to create a measure that counts only the students who are enrolled in the selected classes and have not been enrolled in any other classes outside of the selected ones.

Example : 

 i filtered on class Math & German , the result show me 4 students for each Class . As bellow the selected value : 

Merida	Math
Merida	German

Ajay	Math
Ajay	German

Patrick	Math

John	German

Fabio	Math
Fabio	German

In my case i want to retrive 2 students for each Class .(Count only Student Merida,Patrick and John)

Why ?

The student Ajay and Fabio have more than 2 classes that was filtered  : 

Ajay	Math
Ajay	German
Ajay	English

Fabio	Math
Fabio	German
Fabio	English
Fabio	Computer

 I want to remove all the students who have more classes and non selected on filter

Thanks for help !