DAX calculation

[Anonymous]

In the above table i want a column "Overall Status" next to Status, which should show 'Pass' or 'Fail' for each Name.
Condition : Show 'Pass' only if the person have Pass in all subject else show 'Fail' in "Overall Status"

[rohit_singh]

Hello @Anonymous ,

Try this calculated column in DAX :

Overall Status =

var _grades =

CALCULATE(

CONCATENATEX(VALUES(Grades[Status]), Grades[Status]," , "),

ALLEXCEPT(Grades, Grades[Name])

)

RETURN

if (CONTAINSSTRING(_grades, "Fail"), "Fail", "Pass" )

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Hi @rohit_singh 
In the above scenario i also want to check if any "Status" value for the "Name" is blank or N/A then also "Overall Status" should show "Fail"

Note: blank is not string, its just empty column.

[Anonymous]

can you please confirm what should i put at highlight text
if (CONTAINSSTRING(_grades, "Fail"), "Fail", "Pass" )

[rohit_singh]

Hi @Anonymous ,

_grades is a a variable that we have created. 

var _grades =

CALCULATE(

CONCATENATEX(VALUES(Grades[Status]), Grades[Status]," , "),

ALLEXCEPT(Grades, Grades[Name])

)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Big Thank
just last help if its possible to show only one value for each Name in Overall status
Currently its showing for each status row.

For each name there should be only one Overall status value, not repeating values.

[selimovd]

Hey @Anonymous ,

as I understood you want it as a calculated column.

Then the following approach would work:

Overall Status = 
IF (
    CALCULATE (
        COUNTROWS ( MyTable ),
        ALLEXCEPT ( MyTable, MyTable[Name] ),
        MyTable[Status] = "Fail"
    ) > 0,
    "Fail",
    "Pass"
)

The result would look like that:

You could also solve that with a measure.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd 
In the above scenario i also want to check if any "Status" value for the "Name" is blank or N/A then also "Overall Status" should show "Fail"

Note: blank is not string, its just empty column.

[selimovd · Accepted solution]

Hey @Anonymous ,

sure, you can expant the criteria for other cases.

You could separate them with the double pipe "||" or you use the IN operator:

Overall Status =
IF (
    CALCULATE (
        COUNTROWS ( MyTable ),
        ALLEXCEPT ( MyTable, MyTable[Name] ),
        MyTable[Status] IN { "Fail", "N/A", BLANK () }
    ) > 0,
    "Fail",
    "Pass"
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

[v-jingzhang]

Hi @Anonymous 

I'd like to make a small modification based on @selimovd 's solution. Replace BLANK() with "". 

Best Regards,
Community Support Team _ Jing

[Samarth_18]

HI @Anonymous ,

Create a column as below:-

Overall status = 
    var result = COUNTROWS(FILTER('Table','Table'[status] = "Fail" && 'Table'[Name]= EARLIER('Table'[Name])))
    return IF(result>0,"Fail","Pass")

Output:-

Regards,

Samarth