Forum Discussion

RShackelford's avatar
RShackelford
Frequent Visitor
5 years ago
Solved

Yet Another Consecutive Values Question

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 "...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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" )
     
     
     
     
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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",
1
)
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",
1
)
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" )
 
 
  • RShackelford's avatar
    RShackelford
    Frequent Visitor
    5 years ago

    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. 

     

    ServerDateIndicatorResult
    A1/1/2021GoodDon't care
    A1/2/2021BadDon’t care
    A1/3/2021GoodDon't care
    A1/4/2021Bad2 Bads
    A1/5/2021Bad2 Bads
    B1/1/2021GoodDon't care
    B1/2/2021GoodDon't care
    B1/3/2021GoodDon't care
    B1/4/2021BadDon't care
    B1/5/2021GoodDon't care
    C1/1/2021BadDon't care
    C1/2/2021GoodDon't care
    C1/3/2021Bad3 Bads
    C1/4/2021Bad3 Bads
    C1/5/2021Bad3 Bads
    C1/6/2021GoodDon't care
    C1/7/2021Bad2 Bads
    C1/8/2021Bad2 Bads
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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" )
       
       
       
       
      • RShackelford's avatar
        RShackelford
        Frequent Visitor
        5 years ago

        Jihwan_Kim 

        Thank You! That's exactly what I needed.

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