category wise condition

[HemanthV]

Hello Guys,

I have a data as shown below, I have ERRORKEY, date, INTERFACEID and a STATUS column. I have created a measure which shows Latest records for each INTERFACEID and one ERRORKEY can have multiple INTERFACEID's.

Now i wan't a new status column like, if a error key has multiple INTERFACEID's and if any of the [STATUS] is 'FAILED' for Latest records then 'FAILED' should be displayed for latest records and 'SUCCESS' should be displayed if all of the [Latest Records] got 'SUCCESS' status. 

It will be really helpful if this can be done using a measure, because i am using direct query.

 As shown in below screenshot

I am attaching PBIX for referrence.
https://drive.google.com/file/d/11JXDd9oLTszulNCcYNVwx_O6ZfuuQmZr/view?usp=sharing

Please Help me on this..!!

Thanks in Advance!
@parry2k @amitchandak @Jihwan_Kim

[lbendlin · Accepted solution]

@HemanthV Here is your measure. 

res = 
// get latest record for each interface id
var a=SUMMARIZE(ALLEXCEPT('Sheet1$','Sheet1$'[ERRORKEY]),'Sheet1$'[INTERFACEID],"IF",MAX('Sheet1$'[INTERFACEID]),"MC",max('Sheet1$'[CREATED]))
// lookup status for that date
var b = ADDCOLUMNS(a,"ST",CALCULATE(max('Sheet1$'[STATUS]),FILTER(ALLEXCEPT('Sheet1$','Sheet1$'[ERRORKEY]),'Sheet1$'[INTERFACEID]=[IF] && 'Sheet1$'[CREATED]=[MC])))
// are we on one of the "latest" rows?
var c = COUNTROWS(Filter(b,[MC]=max('Sheet1$'[CREATED])))
// check if at least one row flagged as latest has a failed status
return switch(TRUE(),c=0,max('Sheet1$'[STATUS]),COUNTROWS(filter(b,[ST]="FAILED"))>0,"FAILED","SUCCESS")

[HemanthV]

Hi @lbendlin ,

Thank you so much, It worked...!!!