cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Count of latest status of each job

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

1 ACCEPTED SOLUTION
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.