Forum Discussion
Solved
Filter
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 su...
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)
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.
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.