How can I determine success or failure based on 3 different row results

[X1WMD]

Hi BI world,

I've tried to be self sufficient here but my usual Googling ways haven't proved fruitful this time, so for the first time ever I'm bothering the community for assistance in the hopes that someone better than me can help.

Here's my problem. I have a list of processes that are perpetually running, 3 of them in particular are important for determining whether a bigger job completed successfully. I'm struggling to find a way to report on that for a visual because I'm unsure how to pull out those 3 processes from the noise of the others, especially when the 3 don't always complete in the same order. Here's a basic, trimmed version of what I have:

DateTime Finished	ProcessName	Status
04/01/2022 07:01	Process1	Successful
04/01/2022 06:45	Process4	Successful
04/01/2022 06:39	Process3	Successful
04/01/2022 06:39	Process2	Successful
04/01/2022 05:17	Process1	Successful
04/01/2022 05:02	Process3	Failed
04/01/2022 04:35	Process2	Successful
04/01/2022 04:30	Process4	Failed

I need to be able to report on each group of Process1, Process2 and Process3 success (so if all 3 show successful then TRUE, if any number of them failed then FALSE). There are other processes thrown into the table (in this example process4 but there are lots more), and as you can see they don't always complete sequentially.

I'm really stumped here so any help would be greatly appreciated.

Thank you,

Wes

[AlexanderPrime]

This doesn't quite seem clear, but do you mean you are looking for a way to combine Process 1 through Process 3 to be an "All in one", and the result of the processes are seperate? 

Do these processes have any other information that could be used as an index? aka How do we know that Process 1 at 04/01/2022 05:17 on your example is a different set of results from Process 1 at 04/01/2022 07:01?

[X1WMD]

Hey Alex,

Yes I'm looking to combine each set of 3 processes into a single answer of success or failure. Unforunately there's no other identifier on the processes, but the processes won't start until the previous iteration of it completed, and generally all 3 are completing within their own 2 hour window. So in the (admittedly vague) example above the processes will have started around 4am, and then again around 6am. All the 4am ones would finish before the 6am would start.

Does that help at all?

Thanks again.

[v-rzhou-msft · Accepted solution]

Hi @X1WMD ,

I agree with Alex, it is better for you to add a column that identifies a Run Sequence Number. Here I suggest you to create a calculated column by RANKX(). It will create a rank group by ProcessName, based on the datetime they appear.

Rank =
RANKX (
    FILTER ( 'Table', 'Table'[ProcessName] = EARLIER ( 'Table'[ProcessName] ) ),
    'Table'[DateTime Finished],
    ,
    ASC,
    DENSE
)

Measure:

All in one =
VAR _Status =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Rank] ),
            'Table'[ProcessName] <= "Process3"
        )
    )
RETURN
    IF ( "Failed" IN _Status, "Failed", "True" )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexanderPrime]

I think that without an identifier of some sort, coding them together may be a bit difficult as you'll need a way to distingush a way Process 1 2 and 3 should aligned together. Doing them in timestamp order is a bit risky. 

Is it possible to add a column that identifies a Run Sequence Number? If so you'd be able to get a much simpler measure in that could search for Fail States within that Sequence but it ignores everything but Process 1 2 and 3.