topic Re: Yet Another Consecutive Values Question in DAX Commands and Tips

Yet Another Consecutive Values Question

RShackelford — Thu, 17 Jun 2021 20:30:18 GMT

I've seen this question asked quite a bit, but I can't seem to find a solution that works for me or doesn't thoroughly confuse me.

I'd like to create a calculated column that counts consecutive "bad" indicators. I don't care so much about single instances, good or bad. I'd like an ouput similar to the Result column below.

If measure would work better, that's fine with me. But it's a calculation I'm going to use quite a bit and, ultimately, I'd like to be able to pull some numbers that say "we had X amount of 'consecutive bads' this month on Y amount of servers and the average number of consecutives (in terms of duration, 2.3 consecutive bads or whatever) was Z".

Server	Date	Indicator	Result
A	1/1/2021	Good	Don't care
A	1/2/2021	Bad	Don’t care
A	1/3/2021	Good	Don't care
A	1/4/2021	Bad	2 Bads
A	1/5/2021	Bad	2 Bads
B	1/1/2021	Good	Don't care
B	1/2/2021	Good	Don't care
B	1/3/2021	Good	Don't care
B	1/4/2021	Bad	Don't care
B	1/5/2021	Good	Don't care
C	1/1/2021	Bad	Don't care
C	1/2/2021	Good	Don't care
C	1/3/2021	Bad	3 Bads
C	1/4/2021	Bad	3 Bads
C	1/5/2021	Bad	3 Bads

Re: Yet Another Consecutive Values Question

Jihwan_Kim — Fri, 18 Jun 2021 03:30:55 GMT

Hi, RShackelford

Please check the below picture and the sample pbix file's link down below. It is for creating a new column.

Step one CC =

VAR currentserver = Data[Server]

VAR rankbydate =

RANKX ( FILTER ( data, Data[Server] = currentserver ), Data[Date],, DESC )

VAR flag =

IF (

Data[Indicator] = "Bad"

&& MAXX (

FILTER (

Data,

Data[Server] = currentserver

&& RANKX ( FILTER ( data, Data[Server] = currentserver ), Data[Date],, DESC ) = rankbydate + 1

Data[Indicator]

) = "Bad",

)

VAR flagtwo =

IF (

Data[Indicator] = "Bad"

&& MAXX (

FILTER (

Data,

Data[Server] = currentserver

&& RANKX ( FILTER ( data, Data[Server] = currentserver ), Data[Date],, DESC ) = rankbydate - 1

Data[Indicator]

) = "Bad",

)

RETURN

flag + flagtwo

Result CC =

VAR currentserver = Data[Server]

VAR steponeCCcountrow =

COUNTROWS (

FILTER ( Data, Data[Server] = currentserver && Data[Step one CC] <> BLANK () )

)

RETURN

IF ( NOT ISBLANK ( Data[Step one CC] ), steponeCCcountrow & " Bads" )

https://www.dropbox.com/s/lkgk0voeuqnvsjs/rshackelford.pbix?dl=0

Re: Yet Another Consecutive Values Question

RShackelford — Fri, 18 Jun 2021 19:55:34 GMT

Thank You Jihwan_Kim!

That worked for the dataset I provided in the example. However, I need to separate out the different consecutive instances within the same server (See server C in the table below). My apologies, I should have specified this in the original example.

Server	Date	Indicator	Result
A	1/1/2021	Good	Don't care
A	1/2/2021	Bad	Don’t care
A	1/3/2021	Good	Don't care
A	1/4/2021	Bad	2 Bads
A	1/5/2021	Bad	2 Bads
B	1/1/2021	Good	Don't care
B	1/2/2021	Good	Don't care
B	1/3/2021	Good	Don't care
B	1/4/2021	Bad	Don't care
B	1/5/2021	Good	Don't care
C	1/1/2021	Bad	Don't care
C	1/2/2021	Good	Don't care
C	1/3/2021	Bad	3 Bads
C	1/4/2021	Bad	3 Bads
C	1/5/2021	Bad	3 Bads
C	1/6/2021	Good	Don't care
C	1/7/2021	Bad	2 Bads
C	1/8/2021	Bad	2 Bads

Re: Yet Another Consecutive Values Question

Jihwan_Kim — Fri, 18 Jun 2021 20:38:20 GMT

Hi, RShackelford

Thank you for your feedback.

Please check the below.

Step one Second Version =

VAR currentserver = Data[Server]

VAR currentdate = Data[Date]

VAR flagcumulate =

IF (

Data[Indicator] = "good",

BLANK (),

SUMX (

ADDCOLUMNS (

SUMMARIZE (

FILTER ( Data, Data[Server] = currentserver && Data[Date] <= currentdate ),

Data[Server],

Data[Date],

Data[Indicator]

"@flag", IF ( Data[Indicator] = "good", 1, 0 )

[@flag]

)

RETURN

flagcumulate

Result CC Second Version =

VAR currentstepone = Data[Step one Second Version]

VAR currentserver = Data[Server]

VAR newtable =

FILTER (

Data,

Data[Server] = currentserver

&& Data[Step one Second Version] == currentstepone

)

VAR result =

COUNTROWS ( newtable )

RETURN

IF ( Data[Indicator] = "good" || result <= 1, "Don't care", result & "Bads" )

https://www.dropbox.com/s/lkgk0voeuqnvsjs/rshackelford.pbix?dl=0

Re: Yet Another Consecutive Values Question

RShackelford — Fri, 18 Jun 2021 21:36:52 GMT

Jihwan_Kim

Thank You! That's exactly what I needed.

Your solution is much cleaner than many I've seen.