Forum Discussion
Solved
category wise condition
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 hav...
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 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")
