Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

How to filter one column with 2 conditions

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!

 

 

 

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Syndicated - Outbound

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

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Syndicated - Outbound

@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])
						
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Mariusz
Community Champion
Community Champion

Syndicated - Outbound

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

 

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)