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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors