Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Everyone,
I am trying to create a column with DAX expression that inputs Yes when a student fails an exam for a certain period. For example, I have the following table
Name | Exam | Exam Score |
Steve | Exam 1 | Fail |
Steve | Exam 2 | Pass |
Steve | Exam 3 | Pass |
Bob | Exam 1 | Pass |
Bob | Exam 2 | Fail |
Bob | Exam 3 | Fail |
Jeff | Exam 1 | Fail |
Jeff | Exam 2 | Fail |
Jeff | Exam 3 | Fail |
I wanted to create an additional column called Exam 1 Fail? and it will put Yes for each row if they failed Exam 1. The result table would be:
Name | Exam | Exam Score | Exam 1 Fail? |
Steve | Exam 1 | Fail | Yes |
Steve | Exam 2 | Pass | Yes |
Steve | Exam 3 | Pass | Yes |
Bob | Exam 1 | Pass | No |
Bob | Exam 2 | Fail | No |
Bob | Exam 3 | Fail | No |
Jeff | Exam 1 | Fail | Yes |
Jeff | Exam 2 | Fail | Yes |
Jeff | Exam 3 | Fail | Yes |
How would I go about doing this? I have tried IF statements but I'm having a tough time finding the correct logic that would but Yes for each row. Any help is much appreciated and let me know if I can clarify.
Solved! Go to Solution.
Hi @botaac
You could use something like this.
Fail exam 1 =
VAR Student = Exams[Name]
VAR Resultexam1 = COUNTROWS(
FILTER( Exams, Exams[Name] = Student && Exams[Exam] = "Exam 1" && Exams[Exam Score] = "Fail"))
RETURN
If(Resultexam1 = 1,"Yes","No")
Best regards,
Jeroen
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Exam]="Exam 1"&&Data[Exam Score]="Fail"))>=1,"Yes","No")
Hope this helps.
Hi @botaac
You could use something like this.
Fail exam 1 =
VAR Student = Exams[Name]
VAR Resultexam1 = COUNTROWS(
FILTER( Exams, Exams[Name] = Student && Exams[Exam] = "Exam 1" && Exams[Exam Score] = "Fail"))
RETURN
If(Resultexam1 = 1,"Yes","No")
Best regards,
Jeroen
That is awesome! Thank you so much, Jeroen. Have a nice day!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |