Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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

 

Need Help

3 Replies

  • ElenaN's avatar
    ElenaN
    Icon for Resolver V rankResolver V
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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