Writing a measure to get a single value for repeated rows.

[SDVN]

I want to create a table visual in power BI: subject_id as the first column and "yes/no" column next to it. But each subject has multiple entries(rows) and some rows will have "closed" in the "Yes/no" column and some rows might have "Opened" for a single subject. I want to show only one row per subject id in the table visual and if that sublect has only "Closed" in Yes/no column, the it should be "Closed", if there is a single "Opened" for that subject, it should be "Opened" in the table visual. 

Its more like students pass/fail logic. If the student is passed in all the subject, the status should be "Passed" and if that student is failed in one of the subjects, the status should be "Failed" even though he/she has passed few subjects.

I wrote a function:

IF(    COUNTROWS(FILTER(mytable, mytable[Yes/No] = "Opened")) > 0,    "Opened",    "Closed")

But in the table visual, its giving all the rows of every subject and not just one row per subject as per the above explained logic.  

[SDVN]

Thank you sir! Very helpful! 

[nilendraFabric · Accepted solution]

try this

Status =
IF (
CALCULATE (
COUNTA ( mytable[Yes/No] ),
FILTER ( mytable, mytable[Yes/No] = "Opened" )
) >= 1,
"Opened",
"Closed"
)