Forum Discussion
Helper INeed Assistance with DAX Measure: Filtering Students by Selected Classes
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 !
10 Replies
- amitchandak
Super User
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_Robot0092Hello 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 !
- 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 GermanIn 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 ComputerI 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.
