Forum Discussion
Helper IIcategory 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 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
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")
2 Replies
- lbendlin
Super User
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")
