Forum Discussion

tcburge3's avatar
tcburge3
Icon for Helper I rankHelper I
3 years ago
Solved

DAX command to Status a column based on filtered Rows and IF statements

Hello,

 

I am running into a problem with writing a column that grabs other row item's statuses.

 

I have the following data:

Script IDTest Outcome
1Passed
1Passed
2Passed
2Blocked
2Failed
3Passed
3Failed

 

I want to create a calculated third column titled [Calculated Result Outcome] that looks at the [Test Outcome] column of the other rows and grabs "Blocked", "Failed" or "Passed" if any of the other columns with the same [Script ID] contains those values in the [Test Outcome] column.

These are in order of preference, so if any of the other rows that have the same [Script ID] contain the value "Blocked" then I want the column to say "blocked". If none contain "Blocked", but one says "Failed" then I want to it so "Failed". And then if all the other rows with the same [Script ID] say "Passed" then I want the calculated column to say "Passed". I'm assuming this would be accomplished though a nestled IF statement, but im having trouble filtering and searching through the range of columns that have the same [Script ID].

Thus, my expected result titled [Calculated Result Outcome] would show the following based off of the example:

Script IDTest OutcomeCalculated Result Outcome
1Passed

Passed

1PassedPassed
2PassedBlocked
2BlockedBlocked
2FailedBlocked
3PassedFailed
3FailedFailed

 

Please let me know if you know how to accomplish this. I've been trying for hours to no avail.

 

I will eveutlally do a "Count Distinct" [Script ID]'s and show their [Calculated Result Outcome] in a table.

 

Thanks and please let me know if I can answer any questions or provide anything else that I may have missed that would be helpful. 

  • lukiz84's avatar
    lukiz84
    3 years ago

    Hi,

     

    try:

     

    Outcome = 
    VAR ScriptOutcomes =
        CALCULATETABLE(
            VALUES(TableTests[Test Outcome]),
            FILTER(
                ALL(TableTests),
                TableTests[Script ID] = EARLIER(TableTests[Script ID])
            )
        )
    
    VAR hasPassed = CONTAINSROW(ScriptOutcomes, "Passed")
    VAR hasFailed = CONTAINSROW(ScriptOutcomes, "Failed")
    VAR isBlocked = CONTAINSROW(ScriptOutcomes, "Blocked")

    RETURN 
        IF(
            isBlocked,
            "Blocked",
            IF(
                hasFailed,
                "Failed",
                IF
                    (hasPassed,
                    "Passed",
                    "N/A"
                )
            )
        )

4 Replies

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

     

    try:

     

    Outcome = 
    VAR ScriptOutcomes =
        CALCULATETABLE(
            VALUES(TableTests[Test Outcome]),
            FILTER(
                ALL(TableTests),
                TableTests[Script ID] = EARLIER(TableTests[Script ID])
            )
        )
    
    VAR hasPassed = CONTAINSROW(ScriptOutcomes, "Passed")
    VAR hasFailed = CONTAINSROW(ScriptOutcomes, "Failed")
    VAR isBlocked = CONTAINSROW(ScriptOutcomes, "Blocked")

    RETURN 
        IF(
            isBlocked,
            "Blocked",
            IF(
                hasFailed,
                "Failed",
                IF
                    (hasPassed,
                    "Passed",
                    "N/A"
                )
            )
        )
    • tcburge3's avatar
      tcburge3
      Icon for Helper I rankHelper I
      3 years ago

      This worked! Thank you so much for the help I really appreciate you a ton. This did exactly what I needed it to do.

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      Tried to learn from yours. 

       

      Calculated Result Outcome = 
      VAR CurrentID = 'Table'[Script ID]
      VAR Table1 =
          CALCULATETABLE(
              VALUES('Table'[Test Outcome]),
              FILTER (
                  ALL ('Table'),
                  'Table'[Script ID] = CurrentID
              )
          )
      RETURN
          IF (
              "Blocked" in table1,
              "Blocked",
              IF(
                  "Failed" in table1,
                  "Failed",
                  "Passed"
              )
         )
      • lukiz84's avatar
        lukiz84
        Icon for Memorable Member rankMemorable Member
        3 years ago

        also good but this assumes that it has to have passed if none of the other statuses were set. that's dependent on the underlying data 🙂