I need to filter a table using a DAX measure based on the following conditions:
For each unique UserID, if that user had an Activity of Run on First Day, an Activity of Run on any number of consecutive dates, and followed by any non-Run Activity and a Hobby of Garden, then keep those rows.
For example, consider the following table:
| Date | UserID | Activity | Hobby |
|---|---|---|---|
| 01/05/2020 | 01A | Run | Stamps |
| 01/06/2020 | 01A | Run | Stamps |
| 01/07/2020 | 01A | Walk | Garden |
| 01/05/2020 | 02B | Run | Stamps |
| 01/06/2020 | 02B | Run | Stamps |
| 01/07/2020 | 02B | Run | Garden |
| 01/08/2020 | 02B | Run | Garden |
| 01/05/2020 | 03C | Run | Stamps |
| 01/06/2020 | 03C | Run | Stamps |
| 01/07/2020 | 03C | Walk | Garden |
| 01/08/2020 | 03C | Walk | Garden |
| 01/05/2020 | 04A | Run | Stamps |
| 01/06/2020 | 04B | Run | Stamps |
| 01/05/2020 | 04A | Run | Garden |
| 01/08/2020 | 04B | Run | Garden |
| 01/08/2022 | 04B | Run | Garden |
The first three rows would be kept since UserID 01A ran for two consecutive days then walked and gardened.
The second set of four would be excluded because 02B run four days in a row, but didn’t end the streak with a non-running activity.
Of the next set of four rows, only the first three would be kept, since the fourth day’s non-running activity was not preceded by two or more days of running, despite gardening on that day.
The final five rows would all be excluded, because users had non-consecutive activities and hobbies that do not meet the criteria. I included them to show the nature of the data in the table, which may have non-consecutive events and multiple events on the same day by a single user.