Filter

[Ugo]

Hi,

I need to determine which are the Route numbers that have an associated Number greater than 50 for 3 successive Dates. In this example, the Route 4, 6 and 7 have Numbers greater than 50 for 3 successive Dates. 
Thanks for your help.

 

[Anonymous]

@Ugo 

Seems route 11 is also has 3 values greater than 50. For your requirement, you could first create a flag column then create a result column.

flag column = IF([Number]>50,1)

Result =

var _count= CALCULATE(SUM([flag column]),FILTER('Table',[Route]=EARLIER('Table'[Route])))

Return IF(_count=3,'Table'[Route])

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Ugo]

Thanks for the solution. However, route 11 does not have three consecutive results greater than 50. There is a result of 30 on August 18th.

[Greg_Deckler · Accepted solution]

@Ugo You are going to want something like Cthulhu or MTBF might also provide a pattern:

Cthulhu - Microsoft Power BI Community

Mean Time Between Failure (MTBF) - Microsoft Power BI Community

Both are for doing row-wise comparison operations (which is not trivial in DAX)