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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors