Count if X ocures 3 times in a row

[TBSST]

Hi All, 

I think i have reached a point in PowerBI capabilities, but hopeing a smart person here can say otherwise!

I am trying to get a count of when an event happens 3 times in a row. 

ID	Date	Value
a	1/01/2023	1
a	2/01/2023	1
a	3/01/2023	2
a	4/01/2023	1
b	1/01/2023	1
b	2/01/2023	1
b	3/01/2023	1
b	4/01/2023	2
c	1/01/2023	1
c	2/01/2023	2
c	3/01/2023	1
c	4/01/2023	1
c	5/01/2023	1

I would like to say the max 3 dates = 1, then count 1.  So with the above, it would only count ID c, becasue the last 3 dates are 1 and ignore a and b because they do not have 3 in a row with the most recent dates for the ID. 

I would like like to add in a table a count of how many '1' an ID is up to. So for example, client a would be at 1, client b would be ar 0, and client c would be at 3. 

Any help would be appreciated. 

[OwenAuger · Accepted solution]

Hi @TBSST 

Here's how I would tackle it (PBIX attached):

Latest Run Length = 
VAR MinDate = MIN ( Data[Date] )
VAR MaxDate = MAX ( Data[Date] )
VAR MaxDateBefore1 =
    CALCULATE ( MAX ( Data[Date] ), Data[Value] <> 1 )
VAR MaxDateBefore1_Adj =
    COALESCE ( MaxDateBefore1, MinDate - 1 )
RETURN
    INT ( MaxDate - MaxDateBefore1_Adj )

[# ID with Latest Run Length >= 3] = 
COUNTROWS (
    FILTER ( VALUES ( Data[ID] ), [Latest Run Length] >= 3 )
)

I added a 4th ID "d" with five 1s in a row.

The above measures operate within the current filter context. If there is any special logic regarding date filtering or handling non-consecutive dates, they would need to be adjusted.

Does something like this work for you?

Regards

[TBSST]

Thank you so much! This worked great 🙂

[ValtteriN]

Hi,

Here is one way to do this:


Dax: (last 3 are 1)

Measure =

var _latestdate = CALCULATE(MAX('Table'[Date]),ALL('Table'))

var _currentdate = MAX('Table'[Date])

var _current = MAX('Table'[Value])

var _previous =

CALCULATE (

            MAX('Table'[Value]),

            OFFSET (

                -1,ALLSELECTED('Table'[Date]),ORDERBY(MAX('Table'[Date]))

            ))

var _2previous = CALCULATE (

            MAX('Table'[Value]),

            OFFSET (

                -2,ALLSELECTED('Table'[Date])

                ,ORDERBY(MAX('Table'[Date]))

            ))

return

IF(_current=_previous && _current = _2previous && _latestdate=_currentdate,1,0)

Dax: (how many 1 up to):

Repeat1Count =

var _id = MAX('Table'[ID])

var _Mdate = CALCULATE(MAX('Table'[Date]),ALL('Table'),_id='Table'[ID],'Table'[Value]<>1) //lates date for non 1 value

var _filteredcountof1 =  COUNTROWS(FILTER(ALL('Table'),'Table'[ID]=_id && 'Table'[Date]>_Mdate && 'Table'[Value]=1))

return

_filteredcountof1

End result:

The values are as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/