Filter by 2 columns

[mattmickeyj]

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
ABC2	Science	C

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!

[devanshi]

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

[tamerj1 · Accepted solution]

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

[mattmickeyj]

A little follow up, sorry - if I wanted to then add another filter say to filter it by a test date.  If I have a column called 'test_period' could I then make it into something like this?

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

VAR T$ =
FILTER ( T3, 'RESULTS'[TEST_PERIOD] ="Spring")
RETURN
INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 )  = COUNTROWS (T4) )
)

[tamerj1]

@mattmickeyj 

Yes you can but I would rather leave the test period in the outer filter either as a slicer or part of the visual. In case you want to aggregate the count over multiple selected periods you may use

A_in_all_Subjects =
SUMX (
SUMMARIZE ( 'RESULTS', 'RESULTS'[Student ID], 'RESULTS'[TEST_PEEIOD] ),
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 ) )
)

[mattmickeyj]

Thank you, you knowledge is ace.  The use case for wanting to create a measure for just spring for example is to then be able to compare autumn to spring (e.g. increase of 5% between autumn and spring). Would this be achieved by adding an extra variable to query to filter for spring and then another for autumn so that it could then be displayed in a table.

[mattmickeyj]

Awesome, that's perfect, thank you so much!!!