[DAX] Test if a consecutive series of cells fulfill a certain condition

Hi All,

I am seeking your help again with a task at hand I'm trying to achieve with DAX.

I have a requirement, which is to highlight weeks where:

1. a certain condition is fulfilled for a single cell. I believe this is a relatively straight forward one.
2. a certain condition is fulfilled for a consecutive number of weeks. For example: Highlight the cells if the value exceeds threshold for 3 weeks consecutively. I am attaching a sample file that clearly explains everything with Excel calculations etc...

Is there a way to do this in DAX? I have already done it via Excel equations in the first tab (using many helper rows) just to explain the idea, however, I need to do this in DAX because the real dataset is actually huge.

Note: I also need the ability to filter on those Lines/SKUs which DO violate the condition, because there will be a large list of them, and the user will need to filter. This basically means I also need some sort of a "key" to be able to filter this with in the table.

Thank You!

Picture for Forum.JPG

Good Morning All!

I would really appreciate your insights on this one if possible.

Thanks!

Originally Posted by AliGW

There seems to be only one member who answers anything about DAX - he usually visits once a day, so hopefully he'll see this and comment.

Thank you Ali! I appreciate your amazing effort in moderating the forum.

Originally Posted by rorya

There is probably a simpler solution, but I think this will work:

Please Login or Register  to view this content.

Hi Rory!

Thank you very much for the solution. I appreciate it. I can't claim I fully understand how it works but I'm trying to breakdown the logic.

I think however that the logic is inconsistent for some reason.


When I tried ">3" in the equation, it did this. For some reason it acted as if it's supposed to detect anything greater than or equal 3 and highlighted the first group even though it shouldn't.

Greater than 3.JPG


But when I tried ">5" it did this. This time it decided to behave differently from the previous one, and ignored the last group which is a violation of 5 consecutive cells.

Greater than 5.JPG

Do you have any idea how to resolve that inconsistency? I just need it to highlight when the violation is greater than a certain number of cells. I'm attaching the latest workbook to this reply below.

Thank You!

I think it's working now. Thank you so much for your great help throughout this Rory! I appreciate it.