SUMIF OR - Calculated field in PowerPivot

[DonnaEm]

I am using this as an exercise to learn powerpivot calculated fields. My question: is there a more efficient way to write the formula below? In Excel I'd join up SUMIFs so that's what I did here but I'm sure there must be a better way. The issue is that some classes have "A+","A",A-" etc so my formula is going to end up very long to cater for all types of pass grade.
Would it be more efficient to just add a Case When field to the SQL script to bring the data into the PowerPivot as Pass/Fail? Many thanks in advance for your advice

There are 36 students in the class who each did 3 tests. I've been asked to provide the number who passed each test.

Assessment Distinct Count of Student_ID Students who Passed
Test 1 36 34
Test 2 36 35
Test 3 36 29

The formula to determine how many students passed:

=CALCULATE(
DISTINCTCOUNT('Query 1'[Student_ID])
, FILTER('Query 1','Query 1'[GradeCode]="A")
)
+CALCULATE(
DISTINCTCOUNT('Query 1'[Student_ID])
, FILTER('Query 1','Query 1'[GradeCode]="B")
)
+CALCULATE(
DISTINCTCOUNT('Query 1'[Student_ID])
, FILTER('Query 1','Query 1'[GradeCode]="C")
)

 

[Kazlik]

One options is to make a measure like this and just add more grade codes as needed.

Students who Passed:=CALCULATE(COUNT('Query 1'[Student_ID]),FILTER('Query 1','Query 1'[GradeCode] = "A" || 'Query 1'[GradeCode] = "A+" || 'Query 1'[GradeCode] = "A-" || 'Query 1'[GradeCode] = "B" ))

 

[kitjosh1050]

Kazlik's formula works, but if you have the option, I would recommend bringing in a calculated flag via the SQL case statement. This keeps things tidy and improves performance in PowerPivot. For example: CASE WHEN GradeCode = 'F' THEN 0 ELSE 1 END AS PassFl.

 

[Salle]

Expanding on Kazlik's formula, I would simply count the rows of the table expression returned by the FILTER function:
=COUNTROWS(FILTER('Query 1','Query 1'[GradeCode] = "A" || 'Query 1'[GradeCode] = "A+" || 'Query 1'[GradeCode] = "A-" || 'Query 1'[GradeCode] = "B" ))

 

[DonnaEm]

Thank you very much for your help everyone. I knew there must be a more efficient way. I will probably do as kitjosh suggests and amend my SQL script to bring the data in more tidily in this situation but the other formulas will be useful for another piece of code I'm working on. I'm assuming that || is some kind of separator between the filters. That was the part I was missing. Thanks again - I appreciate the time you took to read my post and comment on it

 

[kitjosh1050]

|| is an OR operator while && is an AND operator in the DAX language.

 

[DonnaEm]

Thanks very much for your help kitjosh. So many languages to learn