How do I achieve the following requirement?

[Anonymous]

Hi all,

I have a requirement where I would like to show in a table visual the ModuleID, Date, and Status for the latest ModuleID per Status. I'll explain visually below.

Let's start with my table.
ModuleID          Date                    Status
English               10/01/2018          Finished
English               10/01/2018          Finished
Science              10/01/2018          Finished
English               10/01/2018          Error

Science              10/01/2018          Finished

English               10/01/2018          Finished

Science              10/01/2018          Finished

Science              09/01/2018          Finished

Science              09/01/2018          Finished

English               09/01/2018         Finished
English               09/01/2018         Finished

English               09/01/2018         Finished

Science              09/01/2018         Finished

English               09/01/2018         Finished

English               08/01/2018         Finished
English               08/01/2018         Finished
English               08/01/2018         Error

I need a way to look through a table for it to return the lastest Date for each ModuleID, based on each Status. For example, the above table contents should return the below rows in the table visual.

ModuleID          Date                   Status

English               10/01/2018         Error
Science              10/01/2018         Finished
English               09/10/2018         Finished

So you can see it see that at least one of the English loads on the 10/01/2018 had an error and therefore the Error status for English is on the 10/01/2018.
Also on the 10/01/2018 is another ModuleID being Science. Each Science load on this date finished and therefore the latest Finished for Science appears on the 10/01/2018.

Scrolling through the rest of the table, I can see Science appears on the 09/01/2018 and all the loads were successful, but as the later (10/01/2018) loads for Science all are as 'finished' we don't want to return finished for the Science loads in the table visual.

The first entire loads for English that all 'Finished' was on the 09/01/2018 so this appears in the table visual.

The only other date in the table is the 08/01/2018, however as this is a 'Error' load for English, and as we already have an English load that has at least Error we do not need to return English for the 08/01/2018.

There may be many more ModuleIDs, and the number of loads attempted for ModuleID may vary day-to-day. There may even be some days where the ModuleID isn't loaded at all.

I hope all this makes sense. I simply require a way of dynamically searching through all records in a table and for the ModuleID to show in the table visual, for each Status (if all loaded rows for a date for a ModuleID have a 'Finished' Status then the ModuleID, Date, and Status should be shown in the table visual.
If one of the loads for a ModuleID, per date has the status of 'Error' then I need that ModuleID, Date and Status shown in the table visual too.

Or to put it another way, show ModuleID, Date, Status GROUPED BY all three columns SORT BY Date DESC LIMIT TOP 1.

To make things a little easier there should only be Finished or Error status. So we could search by those two status' only.

I hope this helps. I do realise this may be a tricky requirement to calculate but if you can help me it will be greatly appreciated.

Thanks in advance.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Add a calculated column in source table:

Count error col = var counterror=CALCULATE(COUNT('Test Data3'[Status]),FILTER(ALLEXCEPT('Test Data3','Test Data3'[ModuleID],'Test Data3'[Date]),'Test Data3'[Status]="Error"))
    return
    
IF(counterror>=1,counterror,0)

Then, add below measure to table visual together with [ModeleID] and [Status].

Latest date =
IF (
    LASTNONBLANK ( 'Test Data3'[Status], 1 ) = "Error",
    CALCULATE ( MAX ( 'Test Data3'[Date] ) ),
    CALCULATE (
        MAX ( 'Test Data3'[Date] ),
        FILTER ( 'Test Data3', 'Test Data3'[Count error col] = 0 )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Thank you Yuliana Gu. This works as I need.

I appreciate your help!