Count of latest status of each job

[selvaskm]

Hi All,

I need to count the latest status for each job from the below sample data. This below table is joined to calendar table through Date Column.

Job              Time                                                  Status

job1	01-03-2024 03:30 PM	Fail
Job1	01-03-2024 02:00 PM	Success
Job1	01-03-2024 10:30 AM	Success
Job2	01-03-2024 04:30 PM	Fail
job2	01-03-2024 10:30 AM	Success
Job3	01-03-2024 06:30 PM	Success
Job3	01-03-2024 04:30 PM	Fail
Job3	01-03-2024 12:30 PM	Fail
Job4	01-03-2024 09:30 AM	Fail
Job4	01-03-2024 10:30 PM	Stopped
Job4	01-03-2024 03:30 PM	Fail
Job4	01-03-2024 02:30 PM	Success

Expected Output:
Fail - 2
Success - 1
Stopped - 1
As total 4 jobs are runned.

Please provide the measure for this scenario

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

expected result measure: =
VAR _t =
    ALL ( data )
VAR _count =
    COUNTROWS (
        FILTER (
            INDEX (
                1,
                _t,
                ORDERBY ( data[time], DESC ),
                ,
                PARTITIONBY ( data[job] ),
                MATCHBY ( data[job], data[time] )
            ),
            data[status] IN VALUES ( data[status] )
        )
    )
RETURN
    _count