Count if multiple conditions are met across multiple columns

[Anonymous]

Hello,

I am trying to figure out how to set up a measure to sum values across multiple columns when two conditions are met.  I need to use this on a card to show how many students need to take each course offered.  Sample data is below.

What I need to do:

I need to count the number of times each course appears in any column and the grade is blank.  

The course number may appear in more than one column, so it needs to look at the course# AND the corresponding grade column to determine if the credits should be counted.   This will tell teachers how many students will need to take the class, so they can determine the number of sections to offer for each course.

So for the data below:  ENG101 shows 2 students, because course1# ENG101 appears in course1# column three times, but only 2 of the GradeC1 columns is blank. 

SCI111 shows 2 students, because it appears in course1# two times, but only one of the GradeC1 columns is blank.  PLUS SCI111 appears in Course2# one time, and GradeC2 is blank.

ENG101           SPN101        SCI111      LIT111    BIO101

         2                         2                        2                     1                   2

Course1#     Credits1         GradeC1          Course2#         Credits2           GradeC2     Course3#       Credits3      GradeC3

ENG101            3                                                      SCI111                   4                                                 BIO101            4               

SPN101             3                          A                         BIO101                 4                           A                    LIT111              3               

SCI111               4                          B                         BIO101                 4                                                  LIT111             3                         C

ENG101            3                          A                         SPN101                3                            B                

ENG101            3                                                       SPN101               3                         

SPN101             3                                                       LIT111                 3                              B

SCI111               4

Any help is appreciated.  I've been trying different things for days, and nothing works!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a blank query in Edit Queries

let
    Source1 = Table.SelectColumns(Sheet4,{"Course1#", "Credits1", "GradeC1"}),
    #"Renamed Columns1" = Table.RenameColumns(Source1,{{"Course1#", "Course name"}, {"Credits1", "Credits"}, {"GradeC1", "Grade"}}),
    Source2 = Table.SelectColumns(Sheet4,{"Course2#", "Credits2", "GradeC2"}),
    #"Renamed Columns2" = Table.RenameColumns(Source2,{{"Course2#", "Course name"}, {"Credits2", "Credits"}, {"GradeC2", "Grade"}}),
    Source3 = Table.SelectColumns(Sheet4,{"Course3#", "Credits3", "GradeC3"}),
    #"Renamed Columns3" = Table.RenameColumns(Source3,{{"Course3#", "Course name"}, {"Credits3", "Credits"}, {"GradeC3", "Grade"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns1", #"Renamed Columns2", #"Renamed Columns3"}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each [Course name] <> null and [Course name] <> "")
in
    #"Filtered Rows"

Apply&&Close

Create measure in Query2

Measure = CALCULATE(COUNTROWS(Query1),FILTER(ALLEXCEPT(Query1,Query1[Course name]),Query1[Grade]=BLANK()))

Add "Course name" column and "Measure" in a muti-row card, turn off "category label".

Set visual level filter for each card

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous can you share your data in excel using onedrive/google drive.