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 else show 'Fail' in "Overall Status"

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
     

10 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

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

      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's avatar
        selimovd
        Most Valuable Professional
        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

      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.

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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