Counts ID if id has values in more than 1 value in a different column

[syasmin25]

Hello,

I am trying to count ID’s that have a “D or F” in more than 1 or more subjects.

I have the following table,

IDPeriodGradeBldg#Subject

101	S1	A	2356	English
102	S1	D	2356	Math
102	S1	F	2356	Science
102	S1	F	2356	English
103	S1	B	2356	Science
103	S2	A	2154	English
104	S1	B	2356	English
104	S2	B	2154	Science
105	S1	C	2356	Math
105	S2	B	2154	English
105	S2	F	2154	Science
106	S2	A	2154	Science

& I have created the following visual. I am trying to create a column/measure that counts the ID if they have have a “D or F” in English and Math or even English, Math and Science. I am also trying to create a column that holds " English and Math" & “English, Math & Science” as values. I am trying to insert a table that would hold those values underneath the table in the image below and calculate their f%. I tried creating conditional columns to separate subjects and then doing an unpivot but realized that it was creating duplicate ID’s and also each ID has subject in a different row. Any help would be appreciated.

I can't seem to attach the file here.

[mahoneypat]

Please try this measure expression, replacing "Grades" for your actual table name.

Students with 1+ D or F =
COUNTROWS (
    FILTER (
        VALUES ( Grades[ID] ),
        CALCULATE (
            COUNTROWS ( Grades ),
            Grades[Grade]
                IN {
                "D",
                "F"
            }
        ) > 0
    )
)

Regards,

Pat

[syasmin25]

I apologize for the late response, the "IN" statement here seems to count if they have failed either D or F, I am trying to count when there is both D and F.

[syasmin25]

Thank you for your help. Unfortunately, I am actually wanting the table to look somewhat like the image below. This is where I am struggling with counts as unpivoting them creates duplicates.