Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
selvaskm
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

job101-03-2024 03:30 PMFail
Job101-03-2024 02:00 PMSuccess
Job101-03-2024 10:30 AMSuccess
Job201-03-2024 04:30 PMFail
job201-03-2024 10:30 AMSuccess
Job301-03-2024 06:30 PMSuccess
Job301-03-2024 04:30 PMFail
Job301-03-2024 12:30 PMFail
Job401-03-2024 09:30 AMFail
Job401-03-2024 10:30 PMStopped
Job401-03-2024 03:30 PMFail
Job401-03-2024 02:30 PMSuccess

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

Please provide the measure for this scenario

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1709361894885.png

 

 

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.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1709361894885.png

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors