Filter student that failed an exam and never passed later

[casiopony]

Hi, 

I am very new to Power BI

I have a table with exams results for students. Each student has a unique "ID number". Students who fail the exam can retake it as many times as they want.

I want to extract or filter the student number "ID number" of students who never passed the exam. 

Any idea?

Thank you

[v-binbinyu-msft · Accepted solution]

Hi @casiopony ,

Please add a new column with below dax formula:

never passed =
VAR cur_id = [id]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[id] = cur_id )
VAR _str =
    CONCATENATEX ( tmp, [result], "," )
RETURN
    IF ( CONTAINSSTRING ( _str, "passed" ), BLANK (), "Yes" )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[casiopony]

That is exactly what I was looking for. Thank you so much 🙏

[sudhav]

This is your table, create 3 main measures 

1)

Totalstudents = DISTINCTCOUNT(StudentTable[id])

2)

passedstudents = CALCULATE(DISTINCTCOUNT(StudentTable[id]),FILTER(StudentTable,StudentTable[result]="passed"))

3)

Neverpassed = [Totalstudents]-[passedstudents]

 after that just take a table visual and take table visual like below and drag the columns like below

Now just goto filter session and apply filters like below, then you will get required list of students who never passed

I hope this is what tyou are searching for.... 

Let me know it is usefull or not....

[casiopony]

Thank you. 

Should I duplicate the table in POwer Query before doing this as I will still need access to the Passed information in other reports?

[TotunG]

Yeah sounds good.

Feel free to mark as a solution if all okay 🙂

[TotunG]

What does the table look like? Even if you can use example data it would help. You basically need to filter by some other column that indicates whether a student has failed the exams/never passed. You could first implement this in Excel or the original table by doing something that tells you whether there has been any instances of a student passing. Like a COUNTIF or IF statement? 

[casiopony]

Hi, thanks for the quick reply

the table looks like this (it has 220 000 row and growing)

id	name	result
456	jack	failed
456	jack	failed
456	jack	passed
123	john	failed
123	john	failed
789	michelle	failed
789	michelle	failed
789	michelle	failed
789	michelle	failed
789	michelle	failed
741	ahmed	failed
741	ahmed	passed

I would like to be able to filter so it shows me that john and michelle never passed the exam. Maybe use a formula in a new column to tag those who never passed so it can then be filtered this way.

[TotunG]

Okay, in Power Query/BI I would filter the Result column by 'Failed' then 'Remove Duplicates' on the ID column (just in case multiple people with same name). This should then give you all the names/IDs of students that have failed/never passed and will be updated live if you refresh it.

[casiopony]

That doesn't work I am afraid. If I start by filter out by 'failed', my report will ignore whether a student id passed on another attempt.

I think i need to a formula that creates a 'never passed' column like this:

id	name	result	never passed
456	jack	failed
456	jack	failed
456	jack	passed
123	john	failed	yes
123	john	failed	yes
789	michelle	failed	yes
789	michelle	failed	yes
789	michelle	failed	yes
789	michelle	failed	yes
789	michelle	failed	yes
741	ahmed	failed
741	ahmed	passed