How to filter one column with 2 conditions

[Anonymous]

Hi,

I have problems with calculating filtered table, my two related tables Registration and Class are below:

Registration

Student_ID	Class
A	C1
A	C3
B	C1
B	C2
B	C3
C	C1
C	C2

Class

Class	School
C1	S1
C2	S1
C3	S2

Now I would like to calculate % of students in S1 also registered classes in S2.

As the example above, the answer will be 66.7% (A&B), and formula should be:
# of students registered S1 & S2 / # of students registered S1

but I couldn't filter two conditions in the same column, which means I couldn't group students who registered in both S1 & S2.

My orginal dax script:

# of students registered S1 & S2 = 
CALCULATE(
DISTINCTCOUNT('Registration'[Student_ID]),
FILTER('Class', AND(RELATED('Class'[School]) = "S1", RELATED('Class'[School]) = "S2"))
)

The above result should be 2 but it turned out to be incorrect. 

Hope someone can help me with it and Thank you in advance!

[amitchandak]

@Anonymous , Join both tables on class an try

countX(filter(summarize(Registration, Registration[Student_ID] , "_1", calculate(count(Registration[Class]), filter(Class, Class[School]="S1"))
													, "_2", calculate(count(Registration[Class]), filter(Class, Class[School]="S2"))
						), [_1]>0 && [_2] >0),[Student_ID])
						

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this.

# of students registered S1 & S2 = 
VAR __s1 = CALCULATETABLE( VALUES( Registration[Student_ID] ), ALL( Class ), 'Class'[School] = "S1" )
VAR __s2 = CALCULATETABLE( VALUES( Registration[Student_ID] ), ALL( Class ), 'Class'[School] = "S2" )
RETURN 
DIVIDE(
   COUNTROWS( INTERSECT( __s1, __s2 ) ),
   COUNTROWS( __s1 )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz:   Really thanks a lot. and I also have a bonus question that we want to know is there any way I can just filter these students from the original Registration table? Since the Registration table also included a column of Subject, I need to know the number of students who registered in both S1 & S2 signed up for each subject.

[Anonymous]

Really thanks a lot for your reply, it really work though i'm not familiar with some of function 🙂