Forum Discussion

mattmickeyj's avatar
mattmickeyj
Frequent Visitor
3 years ago
Solved

Filter by 2 columns

Hi,

 

I may be overthinking this, but I have a table as follows:

 

Student IDSubjectGrade
ABC1MathsA
ABC1EnglishB
ABC1ScienceA
ABC2MathsB
ABC2EnglishA
ABC2ScienceC

 

What I need to calculate is how many students got an A or a B in Maths, English and Science - so the answer based on the above would be 1.

 

I didn't know if the best way would be to try and do this is filter across multiple columns, I tried, but couldn't achieve the result I needed, using something like the following (which is probably way off!)

 

 

A_in_all_Subjects = CALCULATE(COUNTROWS('RESULTS'),
FILTER(RESULTS,
'RESULTS[SUBJECT]="Maths" && 'RESULTS'[GRADE]="A|B" &&
'RESULTS'[SUBJECT]="English" && 'RESULTS'[GRADE]="A|B" &&
'RESULTS'[SUBJECT]="Science" && 'RESULTS'[GRADE]="A|B"
)
)

 

 

Thank you everyone!

  • tamerj1's avatar
    tamerj1
    3 years ago

    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 ) )
    )