cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
tcburge3
Frequent Visitor

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors