Count of Consecutive Values

Hi,

Dealing with a scenario where we have vehicles and their Operational Status, which is either Operational or Not Operational.

Data is snapshotted every day so we have a running operational status. We also have an Is Current Flag in the data to show the latest snapshot.

What I need to do is to classify the number of vehicles (assets) which are Not Operational and split this into Short Term and Long Term groups.

To be classed as Short Term Not Operational they need to be in a status of Not Operational at this current time for 9 days or Less.

To be Classed as Long Term Not Operational they need to be in a Status of Not Operational at this current time for 10 days or more.

Example data for Asset 1 - Asset has been Not Operational for 13 days so is counted as Long Term Not Operational as per below

image

Example data for Asset 2 - Asset has been Not Operational for 2 days so is counted as Short Term Not Operational

image

Example data for Asset 3 - Asset is Operational so is counted as Operational (I don’t need a solution for this, measure created and already in example PBIX enclosed)

image

Example data for Asset 4 - Asset has been Not Operational for 18 days so is counted as Long Term Not Operational as per below

A few things I will add;

  • My rule for the posted data is 10 days or more in the status of Not Operational is classed as Long Term Not Operational. In my real world example this will be set to 60 days
  • I’m dealing with a large data set
  • Unsure if this solution is best covered in DAX or Power Query
  • I may have to create a measure to display how long an asset has been Not Operational for.

Dummy PBIX with data pasted into a table is enclosed.
Short Term vs Long Term Status.pbix (83.0 KB)

Hello @DavieJoe,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the requirements that you’ve mentioned i.e., “Short Term Not Operational” and “Long Term Not Operational”. Below are the two DAX measures alongwith the screenshot of the final results provided -

Not Operational Assets - Short Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        SUMMARIZE(
            StatusData , 
            StatusData[Date] ,
            StatusData[Asset ID] ,
            StatusData[Status] ,
            StatusData[Is Current Flag] ) , 
        ( StatusData[Status] = "Operational" &&
        StatusData[Is Current Flag] = 1 ) ||
        ( StatusData[Status] = "Not Operational" &&
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE(
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] ,
                    StatusData[Status] ,
                    StatusData[Is Current Flag] ) , 
                ( StatusData[Status] = "Operational" &&
                StatusData[Is Current Flag] = 1 ) ||
                ( StatusData[Status] = "Not Operational" &&
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
        ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Diff" , 
    DATEDIFF(
        
        MINX( 
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        StatusData , 
                        StatusData[Date] ,
                        StatusData[Asset ID] ,
                        StatusData[Status] ,
                        StatusData[Is Current Flag] ) , 
                    ( StatusData[Status] = "Operational" &&
                    StatusData[Is Current Flag] = 1 ) ||
                    ( StatusData[Status] = "Not Operational" &&
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
        StatusData[Date] ) , 

        MAXX(
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        StatusData , 
                        StatusData[Date] ,
                        StatusData[Asset ID] ,
                        StatusData[Status] ,
                        StatusData[Is Current Flag] ) , 
                    ( StatusData[Status] = "Operational" &&
                    StatusData[Is Current Flag] = 1 ) ||
                    ( StatusData[Status] = "Not Operational" &&
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
        StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable =
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Status] = "Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Operational" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            [@Status_Define] = "Short Term Not Operational" ) , 
    "@Status_Date" , StatusData[Date] , 
    "@Asset_ID" , StatusData[Asset ID] , 
    "@Original_Status" , StatusData[Status] , 
    "@Is_Current_Flag" , StatusData[Is Current Flag] ,
    "@Status_Define" , [@Status_Define] )

VAR _Results =
COUNTROWS( _fTable )

RETURN
_Results
Not Operational Assets - Long Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        SUMMARIZE(
            StatusData , 
            StatusData[Date] ,
            StatusData[Asset ID] ,
            StatusData[Status] ,
            StatusData[Is Current Flag] ) , 
        ( StatusData[Status] = "Operational" &&
        StatusData[Is Current Flag] = 1 ) ||
        ( StatusData[Status] = "Not Operational" &&
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE(
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] ,
                    StatusData[Status] ,
                    StatusData[Is Current Flag] ) , 
                ( StatusData[Status] = "Operational" &&
                StatusData[Is Current Flag] = 1 ) ||
                ( StatusData[Status] = "Not Operational" &&
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
        ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Diff" , 
    DATEDIFF(
        
        MINX( 
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        StatusData , 
                        StatusData[Date] ,
                        StatusData[Asset ID] ,
                        StatusData[Status] ,
                        StatusData[Is Current Flag] ) , 
                    ( StatusData[Status] = "Operational" &&
                    StatusData[Is Current Flag] = 1 ) ||
                    ( StatusData[Status] = "Not Operational" &&
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
        StatusData[Date] ) , 

        MAXX(
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        StatusData , 
                        StatusData[Date] ,
                        StatusData[Asset ID] ,
                        StatusData[Status] ,
                        StatusData[Is Current Flag] ) , 
                    ( StatusData[Status] = "Operational" &&
                    StatusData[Is Current Flag] = 1 ) ||
                    ( StatusData[Status] = "Not Operational" &&
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
        StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable =
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Status] = "Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Operational" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            [@Status_Define] = "Long Term Not Operational" ) , 
    "@Status_Date" , StatusData[Date] , 
    "@Asset_ID" , StatusData[Asset ID] , 
    "@Original_Status" , StatusData[Status] , 
    "@Is_Current_Flag" , StatusData[Is Current Flag] ,
    "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note:- In the case of 60 days requirements, change the number of days condition accordingly in both the measures.

Thanks and Warm Regards,
Harsh

Short Term vs Long Term Status - Harsh.pbix (89.8 KB)

1 Like

This looks like Wizardry :wink:

:magic_wand: :magic_wand: :magic_wand: :magic_wand: :magic_wand:

@Harsh As always, a massive thank you for taking the time to provide a solution on this, I will check it out and come back to you.

One day, when I grow up, I want my DAX to be as good as yours (well, maybe halfway would be good enough :joy:)

1 Like

Hi @Harsh

A couple of things, which are all down to me. The Is Current Flag is no longer part of this report so I’ve removed this column from the data table. I’ve also updated the table and column names to exactly match what is in my Live report. I wasn’t confident in how to remove the Is Current Flag logic and replace it with the below…

We have two snapshots taken each day, one at 08:00am and one at 16:00am so I’ve duplicated the data that is in the report and given one half the 08:00am snapshot as a reference and the other half 16:00pm.

I wasn’t sure how to add this into your DAX, I basically want the formula to ignore one of the snapshot times entirely, so can we update it to include the 08:00am snapshot data please?

Thanks in advance

Short Term vs Long Term Status - Harsh v1.1.pbix (91.8 KB)

Hello @DavieJoe,

As you know the rules or guidelines of the Forum, the solution is provided based on the initial requirements mentioned into the first post.

For any new scenario’s, members are required to create a new thread accordingly.

Thanks and Warm Regards,
Harsh

1 Like

Yes, fair point. I will repost as a new thread.