longest streak, incl and excl gaps

[BigPatJennings]

Hi,

 I've searched for 'streaks' in this forum but can't see something that fits the bill. I have data where I need to find the longest 'yes'  streaks, where there are three outcomes: yes, no or blank. I need two measures - one that counts blanks as 'no', and one that ignores blanks.

I've attached a sample pbix (and year being text is not a mistake, due to the data I'm dealing with. It's possible that the date could be 2019.7, for example)

The data looks like this, and I've attached a pbix with the desired result.

Thanks in anticipation !

https://drive.google.com/file/d/1s2RmK95cdG0kiAzldhzOV0jznNYHmzX3

YearShiftNameSuccess

2020	1	Aaron	Y
2020	2	Aaron	Y
2020	3	Aaron	N
2021	1	Aaron	Y
2021	2	Aaron	Y
2022	1	Aaron	Y
2022	2	Aaron	Y
2022	3	Aaron	Y
2022	4	Aaron	Y
2020	1	Betty	Y
2020	2	Betty
2020	3	Betty	Y
2021	1	Betty	Y
2021	2	Betty	Y
2022	1	Betty	N
2022	2	Betty	Y
2022	3	Betty	Y
2022	4	Betty	N
2020	1	Charlie	Y
2020	2	Charlie
2020	3	Charlie	Y
2021	1	Charlie	N
2021	2	Charlie	N
2022	1	Charlie	N
2022	2	Charlie	N
2022	3	Charlie	N
2022	4	Charlie	N

Desired result 1 is: Aaron 6, Betty 3, Charlie 1 (blanks count as a 'no')

Desired result 2 is: Aaron 6, Betty 4, Charlie 2 (blanks ignored)

[v-rongtiep-msft]

Hi @BigPatJennings ,

Why Desired result 1 is: Aaron 6, Betty 3, Charlie 1 . How to get the result? I cannot get the pbix file.

Could you please provide the pbix file without privacy information and desired output with more details(Explain to me why  got 6, 3, 1 respectively).

Thank you!

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BigPatJennings]

Hi @v-rongtiep-msft ,

If you make the table readble, you'll see the results are

Aaron YYNYYYYYY (6 Y's is the longest streak)

Betty  Y_YYYNYYN (3 Y's is the longest, 4 Y's is the longest if you exclude the break)

Charlie Y_YNNNNNN (1 longest, 2 if you exclude the break)

I've edited the link to hopefully allow access

https://drive.google.com/file/d/1s2RmK95cdG0kiAzldhzOV0jznNYHmzX3

[v-rongtiep-msft · Accepted solution]

Hi @BigPatJennings ,

Please refer to my pbix file to see if it helps you.

Create 2 columns.

value_1re = var _1= CALCULATE(MIN('Table'[Success]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Success]="Y"))
return 
IF('Table'[Success]=_1,1,BLANK())

value_2re = 

var aa = CALCULATE(MIN('Table'[Index]),FILTER(ALLEXCEPT('Table','Table'[name]),'Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Success]="N"))

var bb = CALCULATE(MAX('Table'[Index]),FILTER(ALLEXCEPT('Table','Table'[name]),'Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Success]<>"N"))

return 

IF(ISBLANK(aa),bb,aa)

Then create measures.

value1 = var _1= CALCULATE(COUNT('Table'[value_1re]),FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])&&'Table'[value_1re]<>BLANK()))
return 
IF(_1=BLANK(),_1+1,_1)

value2 = var _1= CALCULATE(COUNT('Table'[Index]),FILTER(ALL('Table'),'Table'[value_2re]=SELECTEDVALUE('Table'[value_2re])&&'Table'[Name]=SELECTEDVALUE('Table'[Name])&&'Table'[Success]="Y"))
return 
_1

Measure 2 = MAXX(FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])),[value2])

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BigPatJennings]

Fantastic, thank you so much! You've made my day!

[Greg_Deckler]

@BigPatJennings You are looking for Cthulhu. And you will need an Index or something similar. Cthulhu - Microsoft Power BI Community