CODEWITHSUNDEEP
powerbidax
yunlee
yunlee

Reputation: 99

Power BI(DAX) | Getting rows with the same value

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

Upvotes: 0

Views: 3577

Answers (1)

Strictly Funk
Strictly Funk

Reputation: 358

This calculated column works:

FinalStatus = 
if(
    CALCULATE(
        COUNTROWS(OriginalTable),
        FILTER(OriginalTable,
            OriginalTable[ID] = EARLIER(OriginalTable[ID]) && 
            OriginalTable[Status]="Completed"))>0,
"Yes","No")

The idea is to filter the table for rows where the ID matches the ID in question, then filter for rows where status is "Completed", and then count the remaining rows.

enter image description here

Upvotes: 1

Related Questions