To compare and make a new column of the value with same ID

[Roy_W]

Hi

I have problem to make a new column value with same ID.

Here is the example of data:

I need to make a new column to distinguish if "pass/failed/not evaluate" with same ID.

 - If A ever got "passed", then to fill all collums that result="passed" with id=A

 - If B got "failed" and no "passed", then to fill all collums that result="failed" with id=B

 - If someone didn't get "passed" or "failed", then to fill all collums that result="not evaluate" with id=someone

I have attached the pbi file to the question, please kindly help to give some advices, thanks.

file attached 

[tamerj1 · Accepted solution]

Hi @Roy_W 

Please refer to attched sample file with the solution

2022 Summary = 
VAR T1 = 
    CALCULATETABLE ( 
        VALUES ( Query1[passed/failed/not evaluate] ), 
        ALLEXCEPT ( Query1, Query1[id] ) 
    )
RETURN
    SWITCH (
        TRUE ( ),
        "passed" IN T1, "passed",
        "failed" IN T1, "failed",
        "not evaluate"
    )