Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
tcburge3
Helper I
Helper I

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. 

1 ACCEPTED SOLUTION
lukiz84
Memorable Member
Memorable Member

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"
                )
            )
        )
    

View solution in original post

4 REPLIES 4
lukiz84
Memorable Member
Memorable Member

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"
                )
            )
        )
    

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

Tried to learn from yours. 

FreemanZ_0-1666944010906.png

 

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"
        )
   )

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 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.