Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
Hope you can help me, this is what I am looking for:
I have a column called Result, and what I would like to have is a measure that count more than 3 positive consecutive values per product. If you see product "10 or" has consecutive values on weeks (4,5,6), weeks (9,10,11,12) and weeks (16,17,18,19). The condition is that has to be more than 3 consecutive weeks to be counted as 1 value.
The output for this example is Product "10 or" has 3 consecutives values and BIZ has 2 consecutives values. I tried to add a column "Consecutive" to help you to understand. If you see more than 3 consecutive "true" is counted as 1.
3 | 1/13/2019 | 10 or | 57 | False |
4 | 1/20/2019 | 10 or | 60 | True |
5 | 1/27/2019 | 10 or | 63 | True |
6 | 2/3/2019 | 10 or | 64 | True |
7 | 2/10/2019 | 10 or | 64 | False |
8 | 2/17/2019 | 10 or | 60 | False |
9 | 2/24/2019 | 10 or | 61 | True |
10 | 3/3/2019 | 10 or | 62 | True |
11 | 3/10/2019 | 10 or | 63 | True |
12 | 3/17/2019 | 10 or | 64 | True |
13 | 3/24/2019 | 10 or | 56 | True |
14 | 3/31/2019 | 10 or | 48 | False |
15 | 4/7/2019 | 10 or | 46 | False |
16 | 4/14/2019 | 10 or | 49 | True |
17 | 4/21/2019 | 10 or | 51 | True |
18 | 4/28/2019 | 10 or | 55 | True |
19 | 5/5/2019 | 10 or | 70 | True |
20 | 5/12/2019 | 10 or | 60 | False |
21 | 5/19/2019 | 10 or | 56 | False |
22 | 5/26/2019 | 10 or | 53 | False |
23 | 6/2/2019 | 10 or | 53 | False |
24 | 6/9/2019 | 10 or | 56 | True |
25 | 6/16/2019 | 10 or | 55 | False |
26 | 6/23/2019 | 10 or | 59 | True |
27 | 6/30/2019 | 10 or | 59 | False |
28 | 7/7/2019 | 10 or | 54 | False |
29 | 7/14/2019 | 10 or | 56 | True |
30 | 7/21/2019 | 10 or | 56 | False |
2 | 1/6/2019 | BIZ 10 | 64 | False |
3 | 1/13/2019 | BIZ 10 | 72 | True |
4 | 1/20/2019 | BIZ 10 | 72 | False |
5 | 1/27/2019 | BIZ 10 | 77 | True |
6 | 2/3/2019 | BIZ 10 | 100 | True |
7 | 2/10/2019 | BIZ 10 | 66 | False |
8 | 2/17/2019 | BIZ 10 | 93 | True |
9 | 2/24/2019 | BIZ 10 | 76 | False |
10 | 3/3/2019 | BIZ 10 | 85 | True |
11 | 3/10/2019 | BIZ 10 | 92 | True |
12 | 3/17/2019 | BIZ 10 | 97 | True |
13 | 3/24/2019 | BIZ 10 | 71 | False |
14 | 3/31/2019 | BIZ 10 | 75 | True |
15 | 4/7/2019 | BIZ 10 | 79 | True |
16 | 4/14/2019 | BIZ 10 | 73 | False |
17 | 4/21/2019 | BIZ 10 | 72 | False |
18 | 4/28/2019 | BIZ 10 | 59 | False |
19 | 5/5/2019 | BIZ 10 | 49 | False |
20 | 5/12/2019 | BIZ 10 | 85 | True |
21 | 5/19/2019 | BIZ 10 | 67 | False |
22 | 5/26/2019 | BIZ 10 | 68 | True |
23 | 6/2/2019 | BIZ 10 | 58 | False |
24 | 6/9/2019 | BIZ 10 | 73 | True |
25 | 6/16/2019 | BIZ 10 | 70 | False |
26 | 6/23/2019 | BIZ 10 | 79 | True |
27 | 6/30/2019 | BIZ 10 | 78 | False |
28 | 7/7/2019 | BIZ 10 | 79 | True |
29 | 7/14/2019 | BIZ 10 | 88 | True |
30 | 7/21/2019 | BIZ 10 | 89 | True |
Hope you can help me.
It is missed the top titles:
Week,Date,Product,Result,ConsecutiveTrue
The column Result has to have 3 consecutive positive values to be counted as 1. Or if you would like to see it in other way, the column ConsecutiveTrue must have 3 consecutive True to be counted as 1.