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 ID Subject Grade ABC1 Maths A ABC1 English B ABC1 Science A ABC2 Maths B ABC2 English A ...
  • 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 ) )
    )

devanshi's avatar
devanshi
Icon for Helper V rankHelper V
3 years ago

A or B Count = CALCULATE(COUNTROWS('Result'), FILTER('Result'[Subject] IN {"Maths", "English","Science"}) && 'Result'[Grade] IN {"A", "B"}) )