Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX calculation

  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 els...
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    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
     

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • rohit_singh's avatar
      rohit_singh
      Solution Sage
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.