Forum Discussion
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 |
| 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!
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 ) )
)
6 Replies
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 ) )
)Awesome, that's perfect, thank you so much!!!
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) )
)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 ) )
)
A or B Count = CALCULATE(COUNTROWS('Result'), FILTER('Result'[Subject] IN {"Maths", "English","Science"}) && 'Result'[Grade] IN {"A", "B"}) )
