Forum Discussion
Solved
Filter by 2 columns
Hi, I may be overthinking this, but I have a table as follows: Student ID Subject Grade ABC1 Maths A ABC1 English B ABC1 Science A ABC2 Maths B ABC2 English A ...
Hi mattmickeyj
please try
A_in_all_Subjects =
SUMX (
VALUES ( 'RESULTS'[Student ID] ),
VAR T1 =
CALCULATETABLE ( 'RESULTS' )
VAR T2 =
FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
VAR T3 =
FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )
RETURN
INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 ) )
)
tamerj1
Community Champion
Community ChampionHi mattmickeyj
please try
A_in_all_Subjects =
SUMX (
VALUES ( 'RESULTS'[Student ID] ),
VAR T1 =
CALCULATETABLE ( 'RESULTS' )
VAR T2 =
FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
VAR T3 =
FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )
RETURN
INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 ) )
)
Awesome, that's perfect, thank you so much!!!