Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 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")