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
Hello,
I am running into a problem with writing a column that grabs other row item's statuses.
I have the following data:
Script ID | Test Outcome |
1 | Passed |
1 | Passed |
2 | Passed |
2 | Blocked |
2 | Failed |
3 | Passed |
3 | Failed |
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 ID | Test Outcome | Calculated Result Outcome |
1 | Passed | Passed |
1 | Passed | Passed |
2 | Passed | Blocked |
2 | Blocked | Blocked |
2 | Failed | Blocked |
3 | Passed | Failed |
3 | Failed | Failed |
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.
Solved! Go to Solution.
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"
)
)
)
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.
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |