How to create a conditional DAX measures that shows the summarised status of each key

[SaraAkira]

Problem statement: How do I achieve a summarised status for each student for their grades? These students all took Math and Science exams, their status for each subject is shown in the first table. The first table is formed by a connection of 2 separate tables, of which the status has a unique identifier of status_id  (1=pass, 2=fail)

I want to create a summary of each student and their overall status seen from the second table, such that the student name will become the unique identifier. The overall status is achieved such that if the student does not pass BOTH subjects, he or she will have a Fail grade. How can I achieve this, can someone explain to me the logic of the dax measures please, thank you.

First_Table

 

Second_Table

#DAX measures #Entity-relationship diagram #ERD

[VilmarSch]

Overall Status =
MIN ( First_Table[Status] )

[Ashish_Mathur]

Hi,

Drag name to the Table visual and write this measure

Measure = if(calculate(countrows(Data),Data[Status]="Pass")=2,"Pass,"Fail")

Hope this helps.

[amitchandak]

@SaraAkira , You can have in a column in the same table

New column =

var _fail = countx(filter(Table, [name] = earlier([name]) && [Status] = "Fail"), [Name])

return

if(isblank(_fail), "Pass", "Fail")

You can nor create a new table using summarize if needed