Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I need some help with getting a final status per each ID. The table is sorted by time, and IDs are composed of random alphabets and numbers. Currently a final status is in a string format like below. I created a look up table for each status, converting each status to a numerical value as I wished to be prioritized. What I want is, if operations for an ID has at least one "Complete", I want the table to say "yes", and otherwise(no Complete at all) "no". For example of ID "K304R" below, it operated three times with Status of "Completed", "Error", and "Canceled", and thus the result I want would be a "yes".
My intuition was 1) ALLEXCEPT original table with ID and Status, 2) somehow get rows with the same ID(ex "K304R"), 3) somehow get Status for each rows of "K304R", 4) somehow connect Status back to the look up table, 4) get Max value for statuses, 5) return "yes" if the max value is 100, and otherwise "no".
Any help would be really appreciated. Thanks ahead!
OriginalTable
Time | ID | Status |
2022/10/4 10:47AM | 1ZT56 | Error |
2022/10/4 9:47AM | K304R | Completed |
2022/10/4 7:47AM | K304R | Canceled |
2022/10/3 10:47PM | 1ZT56 | Completed |
2022/10/3 7:47AM | PQ534 | Canceled |
2022/10/3 4:47AM | 12PT3 | Error |
2022/10/2 10:40PM | 12PT3 | Error |
2022/10/2 7:47PM | 1ZT56 | Canceled |
2022/10/1 10:47AM | U73RL | Completed |
LookupTable
Status | StatusVal |
Completed | 100 |
Canceled | 0 |
Error | 0 |
Result I want
Time | ID | Status | FinalStatus |
2022/10/4 10:47AM | 1ZT56 | Error | yes |
2022/10/4 9:47AM | K304R | Completed | yes |
2022/10/4 7:47AM | K304R | Canceled | yes |
2022/10/3 10:47PM | 1ZT56 | Completed | yes |
2022/10/3 7:47AM | PQ534 | Canceled | no |
2022/10/3 4:47AM | 12PT3 | Error | no |
2022/10/2 10:40PM | 12PT3 | Error | no |
2022/10/2 7:47PM | 1ZT56 | Canceled | yes |
2022/10/1 10:47AM | U73RL | Completed | yes |
Solved! Go to Solution.
Are you actually looking to leverage LookupTable for other reasons or is its only use to try to get the correct FinalStatus output?
It's actually easier to do this without a helper table. You were on the right track with ALLEXCEPT, but can skip a bunch of the steps you were envisioning by using CONTAINS:
FinalStatus =
CALCULATE(
IF(
CONTAINS( OriginalTable, OriginalTable[Status], "Completed" ),
"yes",
"no"
),
ALLEXCEPT( OriginalTable, OriginalTable[ID] )
)
Are you actually looking to leverage LookupTable for other reasons or is its only use to try to get the correct FinalStatus output?
It's actually easier to do this without a helper table. You were on the right track with ALLEXCEPT, but can skip a bunch of the steps you were envisioning by using CONTAINS:
FinalStatus =
CALCULATE(
IF(
CONTAINS( OriginalTable, OriginalTable[Status], "Completed" ),
"yes",
"no"
),
ALLEXCEPT( OriginalTable, OriginalTable[ID] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |