Counts of 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.

[littlemojopuppy]

Hi @syasmin25 

Try this...

Failing Grade Count = 
    VAR StudentTermSummary =
        SUMMARIZE(
            ALLSELECTED(Grades),
            Grades[ID],
            Grades[Period],
            "FailingGrades",
            CALCULATE(
                COUNTROWS(Grades),
                Grades[Grade] IN {"D", "F"}
            )
        )
    RETURN

    CALCULATE(
        DISTINCTCOUNT(Grades[ID]),
        FILTER(
            StudentTermSummary,
            [FailingGrades] > 0
        ),
        VALUES(Grades[Period])
    )

[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.

[littlemojopuppy]

@syasmin25  how about this?

Failing Grade Count = 
    VAR StudentTermSummary =
        SUMMARIZE(
            ALLSELECTED(Grades),
            Grades[ID],
            Grades[Subject],
            "FailingGrades",
            CALCULATE(
                COUNTROWS(Grades),
                Grades[Grade] IN {"D", "F"}
            )
        )
    RETURN

    CALCULATE(
        DISTINCTCOUNT(Grades[ID]),
        FILTER(
            StudentTermSummary,
            [FailingGrades] > 0
        ),
        VALUES(Grades[Subject])
    )

[syasmin25]

Hello, 

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 again, this seems to make a lot of sense. However, I would like to make combination as the image below, how would I create a table like that to do the counts using the the raw data posted above in the question?

[amitchandak]

@syasmin25 , for this one you might have create two meaures with filters, with formula suggested in last post and then use those measures in matrix, and use Option "Show on Row"

[littlemojopuppy]

Hey @amitchandak dd you notice where someone else offered a solution???  😐

[amitchandak]

@littlemojopuppy , Again no overlap with your Answer. I just suggested display way.  Check - "with formula suggested in last post"

[littlemojopuppy]

🙄