Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DanielSagaon
Frequent Visitor

Count more than 3 consecutive values

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.

 

31/13/2019 10 or57False
41/20/2019 10 or60True
51/27/2019 10 or63True
62/3/2019 10 or64True
72/10/2019 10 or64False
82/17/2019 10 or60False
92/24/2019 10 or61True
103/3/2019 10 or62True
113/10/2019 10 or63True
123/17/2019 10 or64True
133/24/2019 10 or56True
143/31/2019 10 or48False
154/7/2019 10 or46False
164/14/2019 10 or49True
174/21/2019 10 or51True
184/28/2019 10 or55True
195/5/2019 10 or70True
205/12/2019 10 or60False
215/19/2019 10 or56False
225/26/2019 10 or53False
236/2/2019 10 or53False
246/9/2019 10 or56True
256/16/2019 10 or55False
266/23/2019 10 or59True
276/30/2019 10 or59False
287/7/2019 10 or54False
297/14/2019 10 or56True
307/21/2019 10 or56False
21/6/2019BIZ 1064False
31/13/2019BIZ 1072True
41/20/2019BIZ 1072False
51/27/2019BIZ 1077True
62/3/2019BIZ 10100True
72/10/2019BIZ 1066False
82/17/2019BIZ 1093True
92/24/2019BIZ 1076False
103/3/2019BIZ 1085True
113/10/2019BIZ 1092True
123/17/2019BIZ 1097True
133/24/2019BIZ 1071False
143/31/2019BIZ 1075True
154/7/2019BIZ 1079True
164/14/2019BIZ 1073False
174/21/2019BIZ 1072False
184/28/2019BIZ 1059False
195/5/2019BIZ 1049False
205/12/2019BIZ 1085True
215/19/2019BIZ 1067False
225/26/2019BIZ 1068True
236/2/2019BIZ 1058False
246/9/2019BIZ 1073True
256/16/2019BIZ 1070False
266/23/2019BIZ 1079True
276/30/2019BIZ 1078False
287/7/2019BIZ 1079True
297/14/2019BIZ 1088True
307/21/2019BIZ 1089True

 

Hope you can help me.

1 REPLY 1
DanielSagaon
Frequent Visitor

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.