Retrieve Case ID for cases with most recent status 'Bad'

[Anonymous]

Hi Guys,

Im trying to retrieve the most recent case ID's with status "bad" per business domain.

Data sample:

Domain - ID- Status - Date

Domain A -101 - Bad - 01-01-2019

Domain A - 101 - Bad - 02-01-2019

Domain A -101 - Good - 03-01-2019

Domain B - 102 - Good - 01-01-2019

Domain B - 103 - Good - 01-01-2019

Domain A - 104 - Bad - 01-01-2019

The end result for the data sample above would be:

Bad Cases Domain A: 104

Bad Cases Domain B: 

Since case ID 101 was eventually completed, this should not be counted as we take the most recent status.

I have tried the following two measures but that does not result in the correct ID's for status 'Bad'.

Could you please provide any suggestion?

Names of Bad Cases =
CONCATENATEX (
    FILTER (
        VALUES ( Table1[ Item Identifier ] ),
        CALCULATE ( COUNTROWS ( Table1 ) )
            = CALCULATE ( COUNTROWS ( Table1 ), Table1[ Status ] = "Bad" )
    ),
    [ Item Identifier ],
    ", "
)

Count of Bad Cases =
COUNTROWS (
    FILTER (
        VALUES ( Table1[ Item Identifier ] ),
        CALCULATE ( COUNTROWS ( Table1 ) )
            = CALCULATE ( COUNTROWS ( Table1 ), Table1[ Status ] = "Bad" )
    )
) + 0

[ElenaN]

Hello,

In order to get the correct IDs that have a current "Bad" status you can create a new measure as follows:

Max_Status = 
VAR MaxDatePerDomainAndID = CALCULATE ( MAX ( 'Status'[Date] ), ALLEXCEPT ( 'Status', 'Status'[Domain], 'Status'[ID] ) )
RETURN
LOOKUPVALUE('Status'[Status], 'Status'[Date], MaxDatePerDomainAndID)

Regards,

ElenaN

[Anonymous]

Thank you for your reply.

Perhaps I did not explain it well enough: What I am looking for is a list (the individual ID's) of all bad cases per domain. 

So the results would be:

Domain A Bad Status: Case X, Case Y, Case Z

Domain B Bad Status: Case A, Case B

Your solution is also useful but only provides the latest case's status per domain. Do you have any other suggestions?

[v-danhe-msft]

Hi @Anonymous,

What is your desired result? If you want below desired table, you could refer this formula:

Create a calculated table:

Name of bad cases = SUMMARIZE('Table1','Table1'[Domain],"ID",CONCATENATEX(CALCULATETABLE('Table1','Table1'[Status]="Bad"),'Table1'[ID],","))

Result:

If I misunderstand you, could you please post a desired picture if possible.

Also you could download my pbix file to have a view.

Regards,

Daniel He