Create a mesure to check if all rows in one column has only one value

[Penn]

Hi all,

Here's a very simple example. There are 3 students, and there is a date filter on the report.

Student	Course	Exam Date	Result
S001	MBA601	1/08/2019	Fail
S001	MCS601	5/09/2019	Pass
S001	MBA607	7/09/2019	Pass
S001	MBA604	9/09/2019	Pass
S001	MBA605	21/10/2019	Pending
S002	MCS607	7/08/2019	Fail
S002	MBA605	8/09/2019	Pass
S002	MBA603	23/10/2019	Pending
S003	MCS601	5/08/2019	Pass
S003	MBA607	7/09/2019	Pass
S003	MCS603	7/09/2019	Pass
S003	MBA605	8/09/2019	Pass
S003	MBA605	9/10/2019	Pass

I want to create a measure to count the students passed all courses within a certain time frame.

Say if the date filter in from Aug 2019 to Oct 2019, the result should be 1 as only Student S003 has passed all exams in this time frame. But if I adjust the filter to look at Sep 2019 only, the result should be 3 as all 3 students have passed all exams in September.

The measure that I wrote only works then I put it in a table with [Student] column in it. How can I return the value in a card?

Count All Pass = CALCULATE(DISTINCTCOUNT('Table'[Student]), FILTER(ALLEXCEPT('Table', 'Table'[Student]), AND(MAX('Table'[Result]) = "Pass", MIN('Table'[Result]) = "Pass")))

[v-shex-msft · Accepted solution]

Hi @Penn ,

You can try to use following measure formula if it suitable for your requirement:

Passed Student Count=
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( T3 ),
            [Student],
            "A Course", DISTINCTCOUNT ( T3[Course] ),//all courses
            "P Course", CALCULATE ( DISTINCTCOUNT ( T3[Course] ), T3[Result] = "Pass" )//passed courses
        ),
        [A Course] = [P Course]
    )
)

Regards,

Xiaoxin Sheng

[Penn]

This is pure witchcraft! Thanks!

[Penn]

Still no clue